Data held in SQL tables should be normalised - in other words, held in neat multiple tables with complete rows, only one piece of logical data per cell, and with information not being repeated in multiple places. (The "why" is off topic for this article, but it basically helps data maintenance and integrity no end).

Multiple normalised tables can be linked together within select commands and this linking is known as joining; when you specifiy a join, you also specify a criteria to tell MySQL how to make the connection, and that's typically done using a key. Let's see a simple example.

Two tables - bdg containing buildings ....


... and res containing residents living there.


When I connect (join) those tables together, I wish to do so by linking the "bid"s - and the syntax I use is:

select * from bdg, res where bdg.bid = res.bid ;

You'll notice that I DON'T use the word join (I could ... but that's another story). Here's my output:


Which is good - in other words, it's what I expected. BUT ... it might be that I want to see at least one row on my report for each of the incoming rows in (say) my building table - to alert me to buildings that don't match any resident records at all. Than can be done using a LEFT JOIN in my select:

select * from bdg left join res on bdg.bid = res.bid ;
    
which gives:



Regular joins and left joins can be extended to three and more tables - the principle is easy but the syntax less so; let's say that we had a third table called dom containing the names of any internet domains registered to each individual:


A regular join on the (now) three tables is straightforward:

select * from bdg, res, dom where bdg.bid = res.bid and res.rid = dom.rid;

and gives the following result:


The syntax for a three way LEFT JOIN is more complex (and thus the inspiration for this article):

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid;

and gives the following result:


Notice that our report now includes orphan records at both join levels - entries in the bdg table that have no corresponding entry in the res table, and entries in the res table that have no corresponding entry in the dom table.


Should we wish to report on orphan records only, we can do so by testing for NULL fields in fields that may not otherwise have a null value.

Example - looking for all incomplete records:

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where dom.rid is NULL;


Example - looking for all buildings with no residents:

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where res.rid is NULL;


(Hey - you really don't need to join in the domain table for this)

Example - looking for all residents with no domains:

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where dom.rid is NULL and res.rid is not NULL;



Here's a complete set of the commands I used to set up this example - you're welcome to cut and paste it for your own testing and experimentation:



See also More (My)SQL commands

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

MySQL - General
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [2861] MySQL and Java - connectivity past, present, and future thoughts - (2010-07-09)
  [2567] Extra MySQL course dates (2 day course, UK) - (2010-01-08)
  [2561] The future of MySQL - (2010-01-03)
  [2559] Moving the product forward - ours, and MySQL, Perl, PHP and Python too - (2010-01-01)
  [2426] Which version of MySQL am I running? - (2009-09-26)
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [2134] Oracle take over Sun who had taken over MySQL - (2009-04-21)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)

More MySQL commands
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [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
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [3060] INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables - (2010-11-19)
  [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.