MySQL - looking for records in one table that do NOT correspond to records in another table
Q: 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>
(written 2009-03-31)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S157 - More MySQL commands [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)
[159] MySQL - Optimising Selects - (2004-12-21)
[279] Getting a list of unique values from a MySQL column - (2005-04-14)
[449] Matching in MySQL - (2005-09-24)
[494] MySQL - a score of things to remember - (2005-11-12)
[502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
[513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
[515] MySQL - an FAQ - (2005-12-03)
[517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
[567] Combining similar rows from a MySQL database select - (2006-01-17)
[572] Giving the researcher power over database analysis - (2006-01-22)
[581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
[591] Key facts - SQL and MySQL - (2006-02-04)
[673] Helicopter views and tartans - (2006-04-06)
[1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
[1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
[1331] MySQL joins revisited - (2007-09-03)
[1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
[1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
[1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
[2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
[2448] MySQL - efficiency and other topics - (2009-10-10)
[2643] Relating tables with joins in MySQL - (2010-02-21)
[2644] Counting rows in joined MySQL tables - (2010-02-22)
[2645] Optimising and caching your MySQL enquiries - (2010-02-22)
[2647] Removing duplicates from a MySQL table - (2010-02-22)
[3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
[3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
[4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
Some other Articles
Which Version of Java am I running?Answering a question with a questionWhatever next ... the joys of being an employerBusiness Networking for Melksham - 23rd AprilMySQL - looking for records in one table that do NOT correspond to records in another tableWhy most training fails ...Funny Amusing, Funny Amazing and The SmartsHow to tweet automatically from a blogLearning to Twitter / what is Twitter?Hire Car, from Atlanta Airport