MySQL - looking for records in one table that do NOT correspond to records in another table
Archive - Originally posted on "The Horse's Mouth" - 2009-03-31 11:54:09 - Graham EllisQ: Can you give an example where I can retrieve all the records of a table "users" where there are no corresponding records in other table "user_role"
A: No (sorry - I'm too busy to set up tables of these names for a demo) but I DO have an example that show you.
We have a table of agents (realtors) and a table of properties they are selling.
• with a regular join, you get all matching records
&bull with a left join, you get all matching records PLUS a row for all the records in the left hand table that don't have anything matching them in the right table
• with a left join and a "where [fieldname] is NULL, you get all the records in the first table that have nothing matching in the second.
Note - you must use is and not = to compare to NULL; NULL is not a value (it is the absence of a value!) so nothing can be equal to it!
mysql> select agent,aid,agid,locate,sid from agents
join sales on aid=agid;
+--------------------------+-----+------+-------------------+-----+
| agent | aid | agid | locate | sid |
+--------------------------+-----+------+-------------------+-----+
| Kavanaghs | 10 | 10 | Semington | 1 |
| Kavanaghs | 10 | 10 | Melksham | 2 |
| Kavanaghs | 10 | 10 | Atworth | 3 |
| Town and Country Estates | 8 | 8 | Westbury | 4 |
| Town and Country Estates | 8 | 8 | Trowbridge | 5 |
| Halifax | 6 | 6 | Melksham | 6 |
| Halifax | 5 | 5 | Trowbridge | 7 |
| Alder King | 1 | 1 | Trowbridge | 17 |
| Halifax | 5 | 5 | Hilperton | 8 |
| Jayson Kent | 4 | 4 | Melksham | 9 |
| Alder King | 1 | 1 | Trowbridge | 18 |
| Jayson Kent | 4 | 4 | Melksham | 10 |
| DK Residential | 3 | 3 | Semington | 12 |
| DK Residential | 3 | 3 | Hilperton | 13 |
| Connells | 2 | 2 | Westbury | 14 |
| Connells | 2 | 2 | Trowbridge | 15 |
| Jayson Kent | 4 | 4 | Semington | 20 |
| Connells | 2 | 2 | Little Twittering | 21 |
| Greg Pullen | 7 | 7 | Wenduine | 22 |
| Jayson Kent | 4 | 4 | Westbury | 23 |
+--------------------------+-----+------+-------------------+-----+
20 rows in set (0.07 sec)
mysql> select agent,aid,agid,locate,sid from agents
left join sales on aid=agid;
+--------------------------+-----+------+-------------------+------+
| agent | aid | agid | locate | sid |
+--------------------------+-----+------+-------------------+------+
| Alder King | 1 | 1 | Trowbridge | 17 |
| Alder King | 1 | 1 | Trowbridge | 18 |
| Connells | 2 | 2 | Westbury | 14 |
| Connells | 2 | 2 | Trowbridge | 15 |
| Connells | 2 | 2 | Little Twittering | 21 |
| DK Residential | 3 | 3 | Semington | 12 |
| DK Residential | 3 | 3 | Hilperton | 13 |
| Jayson Kent | 4 | 4 | Melksham | 9 |
| Jayson Kent | 4 | 4 | Melksham | 10 |
| Jayson Kent | 4 | 4 | Semington | 20 |
| Jayson Kent | 4 | 4 | Westbury | 23 |
| Halifax | 5 | 5 | Trowbridge | 7 |
| Halifax | 5 | 5 | Hilperton | 8 |
| Halifax | 6 | 6 | Melksham | 6 |
| Greg Pullen | 7 | 7 | Wenduine | 22 |
| Town and Country Estates | 8 | 8 | Westbury | 4 |
| Town and Country Estates | 8 | 8 | Trowbridge | 5 |
| Davies and Davies | 9 | NULL | NULL | NULL |
| Kavanaghs | 10 | 10 | Semington | 1 |
| Kavanaghs | 10 | 10 | Melksham | 2 |
| Kavanaghs | 10 | 10 | Atworth | 3 |
| Andrews | 14 | NULL | NULL | NULL |
| Eddy and Isaac | 16 | NULL | NULL | NULL |
| Boris and Boris | 15 | NULL | NULL | NULL |
+--------------------------+-----+------+-------------------+------+
24 rows in set (0.00 sec)
mysql> select agent,aid,agid,locate,sid from agents
left join sales on aid=agid where sid is NULL;
+-------------------+-----+------+--------+------+
| agent | aid | agid | locate | sid |
+-------------------+-----+------+--------+------+
| Davies and Davies | 9 | NULL | NULL | NULL |
| Andrews | 14 | NULL | NULL | NULL |
| Eddy and Isaac | 16 | NULL | NULL | NULL |
| Boris and Boris | 15 | NULL | NULL | NULL |
+-------------------+-----+------+--------+------+
4 rows in set (0.01 sec)
mysql>