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

join;MySQLselect

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.

Best Answer

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.

Related Solutions

Mysql – get column from too many tables in thesql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Mysql – Does the MySQL primary key affect the efficiency of a SELECT query

All things InooDB, the PRIMARY KEY has a major role. As ypercube mentioned towards end of answer, InnoDB uses a clustered key structure, and the key used for clustering is the PRIMARY KEY.

It is very important to have as small footprint for PRIMARY KEY as possible: the depth of the index is the minimum payment you have to pay so as to get to a leaf node. The depth of an InnoDB PRIMARY KEY is the starting payment for any index search.

My general advice is to use AUTO_INCREMENT primary keys for InnoDB. Here are three blog posts of mine which support this:

Best Answer

ABOUT YOUR QUESTION

Related Solutions

Mysql – get column from too many tables in thesql

Mysql – Does the MySQL primary key affect the efficiency of a SELECT query

Related Question