phpmyadmin Question posted in
The official documentation can be found here.

MySQL returning not returning all the records – Phpmyadmin

I have two tables listed below:-

  1. football – It has 2267 unique records.
  2. football_assets – It has 2716 unique records.

Now I’m running following mysql query in phpmyadmin :- 

select * from football_assets where pair in (SELECT DISTINCT pair FROM `football`);

The query returns only 2191 players but it should give 2267 records. I want to know why??

thanks in advance.

Tags:

2

Answers


  1. 0

    The results you are getting show that there are 2267-2191 = 76 records in football_assets whose pair cannot be found in football.

    You can exhibit these records by changing the IN in your query to NOT IN :

    SELECT * 
FROM football_assets 
WHERE pair NOT IN (
    SELECT DISTINCT pair FROM `football`
);
    Login or Signup to reply.
  2. 0

    First, the select distinct is redundant in the subquery. A WHERE clause does not change the number of rows. So, you should write:

    select fa.*
from football_assets fa
where fa.pair in (select f.pair from football f);

    Obviously, you have some pairs that are not in both tables. I strongly encourage you to use not exists or left join/where to find them:

    select fa.*
from football_assets fa
where not exists (select 1 from football f where fa.pair = f.pair);

    Do not use not in. It will return no rows at all if even a single value of pair is NULL in football. That is because of how NULLs work in SQL. For this reason, I strongly discourage not in with subqueries.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search