MySQL joins - using left join and right join to find orphan rows

If you're joining several tables together, you can do so in such a way that you show only the combined results, or in such a way that you can also (or only) show rows in one table that do not match rows in the other table.

A PRACTICAL EXAMPLE

Sound complicated? Let's see an example. I have a table (let's put in on the left) of Estate Agents and on the right a table of properties for sale.

1. Would give me a table of all the properties for sale through agents, and agent details

2. Would give me the same (all the properties for sale through agents) with an additional line for each agent who's not got any properties in our list.

3. Would give me just a list of agents with no properties listed

4. Would give me a table of all the properties on our list, whether or not they're for sales through any of the agents listed in our agent table

5. Would give me just the properties that aren't with one of our agents.

DETAILED THEORY

If you're linking two tables together in MySQL, there are five different types of result that you might want to achieve.

1. You might want to see only those combined (joined) records for which data exists in both tables. That's a regular join.

2. You might want to see all the combined records, AND in addition an extra record for each row in the left table which doesn't match any rows at all in the right table; we'll call these extra records in the left table "Orphan rows".

3. You might want to combine the tables so that you can see ONLY the orphan (unmatched) records in the left table.

4. You might want to see all the combined records AND an additional record for each orphan in the right table

5. and finally you might want to see orphan rows from the right table ONLY.

AS MYSQL STATEMENTS

Let's see that in SQL statement terms:

# Regular join - shows all MATCHING records
select agent, town, phone, aid, agid, sid, locate, asking
         from agents join sales on aid = agid;

# Left join - ALSO show agents with no properties listed
select agent, town, phone, aid, agid, sid, locate, asking
         from agents left join sales on aid = agid;

# right join - ALSO show properties with no agents listed
select agent, town, phone, aid, agid, sid, locate, asking
         from agents right join sales on aid = agid;

# Left join and NULL - only show agents with no properties listed
select agent, town, phone, aid, agid, sid, locate, asking
         from agents left join sales on aid = agid where sid is NULL;

# right join and NULL - only show properties with no agents listed
select agent, town, phone, aid, agid, sid, locate, asking
         from agents right join sales on aid = agid where aid is NULL;

and in terms of results:

TABLE OF AGENTS

select * from agents;

---------------------------+-----------------+------------------+--------------+-----+
| agent | street | town | phone | aid |
---------------------------+-----------------+------------------+--------------+-----+
| Alder King | 63, Fore Street | Trowbridge | 01225 754301 | 1 |
| Connells | | Trowbridge | 01225 754391 | 2 |
| DK Residential | 9 Church Street | Trowbridge | 01225 759123 | 3 |
| Jayson Kent | 35 Market Place | Melksham | 01225 707798 | 4 |
| Halifax | | Trowbridge | 01225 752375 | 5 |
| Halifax | | Melksham | 01225 703773 | 6 |
| Greg Pullen | | Devizes | 01380 724040 | 7 |
| Town and Country Estates | 16 High Street | Westbury | 01373 824444 | 8 |
| Davies and Davies | | Bradford on Avon | 01225 867555 | 9 |
| Kavanaghs | 13 High Sreet | Melksham | 01225 706860 | 10 |
---------------------------+-----------------+------------------+--------------+-----+
10 rows in set (0.00 sec)

TABLE OF PROPERTIES

select * from sales;

-------+------------+--------+-----------------------------------------------------+-----+
| agid | locate | asking | brief | sid |
-------+------------+--------+-----------------------------------------------------+-----+
| 10 | Semington | 225000 | Double fronted two story apartment | 1 |
| 10 | Melksham | 195000 | Attractive detached older style family home | 2 |
| 10 | Atworth | 237500 | Attractive four bedroom village home | 3 |
| 8 | Westbury | 152000 | Three bedroomed semi | 4 |
| 8 | Trowbridge | 205000 | Four bedroom detached in cul de sac | 5 |
| 6 | Melksham | 229950 | Four bedroomed semi in Aloeric school catchment | 6 |
| 5 | Trowbridge | 335000 | Detached family home with no onward chain | 7 |
| 1 | Trowbridge | 96950 | Well presented one bedroom flat | 17 |
| 5 | Hilperton | 279950 | Modern detached home | 8 |
| 4 | Melksham | 149950 | For all those non-gardeners | 9 |
| 1 | Trowbridge | 220000 | semidetached family home | 18 |
| 4 | Melksham | 127500 | Within a presigious retirement complex | 10 |
| 11 | Melksham | 465000 | Semidetached Spa house on town outskirts | 11 |
| 3 | Semington | 222000 | A pleasing detached modern residence | 12 |
| 3 | Hilperton | 465000 | An individual detached quality home | 13 |
| 2 | Westbury | 140000 | Three bedroom semidetached house | 14 |
| 2 | Trowbridge | 116000 | Larger than average first floor flat | 15 |
| 11 | Melksham | 275000 | Grade II Georgian townhouse requiring modernisation | 16 |
-------+------------+--------+-----------------------------------------------------+-----+
18 rows in set (0.00 sec)

REGULAR JOIN

select agent, town, phone, aid, agid, sid, locate, asking
from agents join sales on aid = agid;

---------------------------+------------+--------------+-----+------+-----+------------+--------+
| agent | town | phone | aid | agid | sid | locate | asking |
---------------------------+------------+--------------+-----+------+-----+------------+--------+
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 1 | Semington | 225000 |
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 2 | Melksham | 195000 |
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 3 | Atworth | 237500 |
| Town and Country Estates | Westbury | 01373 824444 | 8 | 8 | 4 | Westbury | 152000 |
| Town and Country Estates | Westbury | 01373 824444 | 8 | 8 | 5 | Trowbridge | 205000 |
| Halifax | Melksham | 01225 703773 | 6 | 6 | 6 | Melksham | 229950 |
| Halifax | Trowbridge | 01225 752375 | 5 | 5 | 7 | Trowbridge | 335000 |
| Alder King | Trowbridge | 01225 754301 | 1 | 1 | 17 | Trowbridge | 96950 |
| Halifax | Trowbridge | 01225 752375 | 5 | 5 | 8 | Hilperton | 279950 |
| Jayson Kent | Melksham | 01225 707798 | 4 | 4 | 9 | Melksham | 149950 |
| Alder King | Trowbridge | 01225 754301 | 1 | 1 | 18 | Trowbridge | 220000 |
| Jayson Kent | Melksham | 01225 707798 | 4 | 4 | 10 | Melksham | 127500 |
| DK Residential | Trowbridge | 01225 759123 | 3 | 3 | 12 | Semington | 222000 |
| DK Residential | Trowbridge | 01225 759123 | 3 | 3 | 13 | Hilperton | 465000 |
| Connells | Trowbridge | 01225 754391 | 2 | 2 | 14 | Westbury | 140000 |
| Connells | Trowbridge | 01225 754391 | 2 | 2 | 15 | Trowbridge | 116000 |
---------------------------+------------+--------------+-----+------+-----+------------+--------+
16 rows in set (0.00 sec)

ALL JOINED ROWS. ALSO AGENTS WITH NO PROPERTIES FOR SALE

select agent, town, phone, aid, agid, sid, locate, asking
from agents left join sales on aid = agid;

---------------------------+------------------+--------------+-----+------+------+------------+--------+
| agent | town | phone | aid | agid | sid | locate | asking |
---------------------------+------------------+--------------+-----+------+------+------------+--------+
| Alder King | Trowbridge | 01225 754301 | 1 | 1 | 17 | Trowbridge | 96950 |
| Alder King | Trowbridge | 01225 754301 | 1 | 1 | 18 | Trowbridge | 220000 |
| Connells | Trowbridge | 01225 754391 | 2 | 2 | 14 | Westbury | 140000 |
| Connells | Trowbridge | 01225 754391 | 2 | 2 | 15 | Trowbridge | 116000 |
| DK Residential | Trowbridge | 01225 759123 | 3 | 3 | 12 | Semington | 222000 |
| DK Residential | Trowbridge | 01225 759123 | 3 | 3 | 13 | Hilperton | 465000 |
| Jayson Kent | Melksham | 01225 707798 | 4 | 4 | 9 | Melksham | 149950 |
| Jayson Kent | Melksham | 01225 707798 | 4 | 4 | 10 | Melksham | 127500 |
| Halifax | Trowbridge | 01225 752375 | 5 | 5 | 7 | Trowbridge | 335000 |
| Halifax | Trowbridge | 01225 752375 | 5 | 5 | 8 | Hilperton | 279950 |
| Halifax | Melksham | 01225 703773 | 6 | 6 | 6 | Melksham | 229950 |
| Greg Pullen | Devizes | 01380 724040 | 7 | NULL | NULL | NULL | NULL |
| Town and Country Estates | Westbury | 01373 824444 | 8 | 8 | 4 | Westbury | 152000 |
| Town and Country Estates | Westbury | 01373 824444 | 8 | 8 | 5 | Trowbridge | 205000 |
| Davies and Davies | Bradford on Avon | 01225 867555 | 9 | NULL | NULL | NULL | NULL |
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 1 | Semington | 225000 |
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 2 | Melksham | 195000 |
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 3 | Atworth | 237500 |
---------------------------+------------------+--------------+-----+------+------+------------+--------+
18 rows in set (0.00 sec)

ALL JOINED ROWS - ALSO ALL PROPERTIES NOT WITH A LISTED AGENT

select agent, town, phone, aid, agid, sid, locate, asking
from agents right join sales on aid = agid;

---------------------------+------------+--------------+------+------+-----+------------+--------+
| agent | town | phone | aid | agid | sid | locate | asking |
---------------------------+------------+--------------+------+------+-----+------------+--------+
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 1 | Semington | 225000 |
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 2 | Melksham | 195000 |
| Kavanaghs | Melksham | 01225 706860 | 10 | 10 | 3 | Atworth | 237500 |
| Town and Country Estates | Westbury | 01373 824444 | 8 | 8 | 4 | Westbury | 152000 |
| Town and Country Estates | Westbury | 01373 824444 | 8 | 8 | 5 | Trowbridge | 205000 |
| Halifax | Melksham | 01225 703773 | 6 | 6 | 6 | Melksham | 229950 |
| Halifax | Trowbridge | 01225 752375 | 5 | 5 | 7 | Trowbridge | 335000 |
| Alder King | Trowbridge | 01225 754301 | 1 | 1 | 17 | Trowbridge | 96950 |
| Halifax | Trowbridge | 01225 752375 | 5 | 5 | 8 | Hilperton | 279950 |
| Jayson Kent | Melksham | 01225 707798 | 4 | 4 | 9 | Melksham | 149950 |
| Alder King | Trowbridge | 01225 754301 | 1 | 1 | 18 | Trowbridge | 220000 |
| Jayson Kent | Melksham | 01225 707798 | 4 | 4 | 10 | Melksham | 127500 |
| NULL | NULL | NULL | NULL | 11 | 11 | Melksham | 465000 |
| DK Residential | Trowbridge | 01225 759123 | 3 | 3 | 12 | Semington | 222000 |
| DK Residential | Trowbridge | 01225 759123 | 3 | 3 | 13 | Hilperton | 465000 |
| Connells | Trowbridge | 01225 754391 | 2 | 2 | 14 | Westbury | 140000 |
| Connells | Trowbridge | 01225 754391 | 2 | 2 | 15 | Trowbridge | 116000 |
| NULL | NULL | NULL | NULL | 11 | 16 | Melksham | 275000 |
---------------------------+------------+--------------+------+------+-----+------------+--------+
18 rows in set (0.00 sec)

AGENTS WITH NO PROPERTIES IN OUR TABLE

PROPERTIES WITHOUT AN AGENT IN OUR TABLES

Link to table details

See also MySQL training course

More MySQL Commands
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

SQL Primer as Used in MySQL
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [270] NULL in MySQL - (2005-04-06)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.