Select records in one table but not in another, when the second table is not matching on primary key

Question

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a vestige id column (which is actually unused in the application at the moment). The cardinal table's primary key is id. The column which should join the tables is sku. These are the tables (relevant fields only, there are other fields that I didn't paste here):

mysql> describe list;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id              | int(11)      | NO   |     | NULL    |       |
| name            | varchar(63)  | YES  |     | NULL    |       |
| sku             | bigint(20)   | NO   | PRI | 0       |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> describe cardinal;
+------------------+-------------+------+-----+---------+----------------+
| Field            | Type        | Null | Key | Default | Extra          |
+------------------+-------------+------+-----+---------+----------------+
| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| sku              | bigint(20)  | YES  | MUL | NULL    |                |
+------------------+-------------+------+-----+---------+----------------+

Here are the amount of records in each table:

mysql> SELECT count(*) FROM list;
+----------+
|  2677513 |
+----------+

mysql> SELECT count(*) FROM cardinal;
+----------+
|   970924 |
+----------+

Every record in the cardinal table has a valid entry for the sku column, and all of those valid entries do exist in the list table. There do exist some dupes for sku in the cardinal table.

I need all the records in the list table which do not have a corresponding entry in the cardinal table. However, all the methods that I'm trying are returning either all the records from the list table, or no records at all!

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;
+----------+
| count(*) |
+----------+
|  2677513 |
+----------+
1 row in set (0.49 sec)

mysql> SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

The first query obviously doesn't know to match on the sku column. But the second one looks like it should work based on other answers that I've seen online.

I expect about 1700000 - 2000000 rows to be returned. But certainly not 0 or the full table. How should I word the query? This is MySQL 5.5.33 running in Amazon RDS.

Answer 1

MySQL, in fact, all SQL products really barf at

c.id=NULL

It causes confusion. You really mean

c.id IS NULL

You need to change it to

SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id is null;

Give it a Try !!!

ABOUT YOUR QUESTION

Your first query

SELECT count(*) FROM list l LEFT JOIN cardinal c ON c.id=null;

resembles a natural join where nothing matches on the cardinal table, so the count 2677513 is correct.

Your second query

SELECT count(*) FROM list l LEFT OUTER JOIN cardinal c ON l.sku=c.sku where c.id=null;

is little more explicit, but the c.id=null makes it fail, thus getting 0 as a count.