Training, Open Source computer languages

Perl • PHP • Python • MySQL • httpd / Tomcat • Tcl • Ruby • Java • C and C++ • Linux • CSS

Search our site for:
Print friendly page
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
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

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

--------------------+------------------+--------------+-----+------+------+--------+--------+
| agent | town | phone | aid | agid | sid | locate | asking |
--------------------+------------------+--------------+-----+------+------+--------+--------+
| Greg Pullen | Devizes | 01380 724040 | 7 | NULL | NULL | NULL | NULL |
| Davies and Davies | Bradford on Avon | 01225 867555 | 9 | NULL | NULL | NULL | NULL |
--------------------+------------------+--------------+-----+------+------+--------+--------+
2 rows in set (0.00 sec)

PROPERTIES WITHOUT AN AGENT IN OUR TABLES

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

--------+------+-------+------+------+-----+----------+--------+
| agent | town | phone | aid | agid | sid | locate | asking |
--------+------+-------+------+------+-----+----------+--------+
| NULL | NULL | NULL | NULL | 11 | 11 | Melksham | 465000 |
| NULL | NULL | NULL | NULL | 11 | 16 | Melksham | 275000 |
--------+------+-------+------+------+-----+----------+--------+
2 rows in set (0.00 sec)

Link to table details

See also MySQL training course
Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site
Related Material

More MySQL Commands
SQL Primer as Used in MySQL
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, 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.
Public Course Enquiry / Booking
Courses that run regularly at our UK training Centre.
[Schedule] - [About] - [Book]
MySQL solutions
Copying databases and tables in MySQL
MySQL joins - using left join and right join to find orphan rows
MySQL SELECT - JOIN versus UNION, WHERE versus HAVING, GROUP etc.
Left Joins to link three or more tables
Management overview of MySQL - background and Philosophies
Justification - MySQL v Oracle, Open Source v Commercial
MySQL version 5, PHP version 5 and mysqli
Mapping database rows to objects in a class
Load balancing and robustness - httpd, tomcat, PHP, Servlets, MySQL
MySQL and PHP - enquiry tool for ad-hoc requirements
Example - PHP form, Image upload. Store in MySQL database. Retrieve.
When to denormalise your MySQL data
Many to many table mapping (Pivot tables)
Replicating servers in MySQL - Why and how
Interfacing applications to a MySQL database engine
MySQL Extras
Windows - running Apache and MySQL from a CD
Solution Centre - all article listing
© WELL HOUSE CONSULTANTS LTD., 2008: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 707126 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho