Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
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
  [158] - ()
  [159] - ()
  [279] - ()
  [449] - ()
  [494] - ()
  [502] - ()
  [513] - ()
  [515] - ()
  [517] - ()
  [567] - ()
  [572] - ()
  [581] - ()
  [591] - ()
  [673] - ()
  [1213] - ()
  [1235] - ()
  [1331] - ()
  [1574] - ()
  [1735] - ()
  [1904] - ()
  [2110] - ()
  [2259] - ()
  [2448] - ()
  [2643] - ()
  [2644] - ()
  [2645] - ()
  [2647] - ()
  [3061] - ()
  [3270] - ()
  [4481] - ()

SQL Primer as Used in MySQL
  [158] - ()
  [270] - ()
  [494] - ()
  [502] - ()
  [515] - ()
  [591] - ()
  [2240] - ()
  [3060] - ()
  [3061] - ()
  [4007] - ()

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.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-my ... -rows.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard