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.