SQL
Below are the pages in the group SQL:
Number of pages in the SQL group: 2.
MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
found here: http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
In a database such as MySQL, data is divided into a series of tables (the "why" is beyond what I'm writing today) which are then connected together in SELECT commands to generate the output required. I find when I'm running MySQL training, people often get confused between all the join flavours. Let me give you an example to see how it works.
mysql> select * from demo_people; +------------+--------------+------+ | name | phone | pid | +------------+--------------+------+ | Mr Brown | 01225 708225 | 1 | | Miss Smith | 01225 899360 | 2 | | Mr Pullen | 01380 724040 | 3 | +------------+--------------+------+ 3 rows in set (0.00 sec) mysql> select * from demo_property; +------+------+----------------------+ | pid | spid | selling | +------+------+----------------------+ | 1 | 1 | Old House Farm | | 3 | 2 | The Willows | | 3 | 3 | Tall Trees | | 3 | 4 | The Melksham Florist | | 4 | 5 | Dun Roamin | +------+------+----------------------+ 5 rows in set (0.00 sec) mysql>
If I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:
mysql> select name, phone, selling from demo_people join demo_property on demo_people.pid = demo_property.pid; +-----------+--------------+----------------------+ | name | phone | selling | +-----------+--------------+----------------------+ | Mr Brown | 01225 708225 | Old House Farm | | Mr Pullen | 01380 724040 | The Willows | | Mr Pullen | 01380 724040 | Tall Trees | | Mr Pullen | 01380 724040 | The Melksham Florist | +-----------+--------------+----------------------+ 4 rows in set (0.01 sec) mysql>
If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join - thus ensuring (in my example) that every PERSON gets a mention:
mysql> select name, phone, selling from demo_people left join demo_property on demo_people.pid = demo_property.pid; +------------+--------------+----------------------+ | name | phone | selling | +------------+--------------+----------------------+ | Mr Brown | 01225 708225 | Old House Farm | | Miss Smith | 01225 899360 | NULL | | Mr Pullen | 01380 724040 | The Willows | | Mr Pullen | 01380 724040 | Tall Trees | | Mr Pullen | 01380 724040 | The Melksham Florist | +------------+--------------+----------------------+ 5 rows in set (0.00 sec) mysql>
If I do a RIGHT JOIN, I get all the records that match and IN ADDITION I get an extra record for each unmatched record in the right table of the join - in my example, that means that each property gets a mention even if we don't have seller details:
mysql> select name, phone, selling from demo_people right join demo_property on demo_people.pid = demo_property.pid; +-----------+--------------+----------------------+ | name | phone | selling | +-----------+--------------+----------------------+ | Mr Brown | 01225 708225 | Old House Farm | | Mr Pullen | 01380 724040 | The Willows | | Mr Pullen | 01380 724040 | Tall Trees | | Mr Pullen | 01380 724040 | The Melksham Florist | | NULL | NULL | Dun Roamin | +-----------+--------------+----------------------+ 5 rows in set (0.00 sec) mysql>
An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatibility and doesn't add an extra capabilities.
Left Joins to link three or more tables
found here: http://www.wellho.net/solutions/mysql-left-joins-to-link-three-or-more-tables.html
MANY-TABLE JOINS IN MYSQL - BACKGROUND
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 ....
-------+-----+ | name | bid | -------+-----+ | 404 | 1 | | 405 | 2 | -------+-----+
... and res containing residents living there.
---------+------+-----+ | person | bid | rid | ---------+------+-----+ | Graham | 1 | 101 | | Lisa | 1 | 102 | ---------+------+-----+
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:
-------+-----+--------+------+-----+ | name | bid | person | bid | rid | -------+-----+--------+------+-----+ | 404 | 1 | Graham | 1 | 101 | | 404 | 1 | Lisa | 1 | 102 | -------+-----+--------+------+-----+
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:
-------+-----+--------+------+------+ | name | bid | person | bid | rid | -------+-----+--------+------+------+ | 404 | 1 | Graham | 1 | 101 | | 404 | 1 | Lisa | 1 | 102 | | 405 | 2 | NULL | NULL | NULL | -------+-----+--------+------+------+
THREE WAY JOINS
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:
------------------------+------+-----+ | domain | rid | did | ------------------------+------+-----+ | www.grahamellis.co.uk | 101 | 201 | | www.sheepbingo.co.uk | 101 | 202 | ------------------------+------+-----+
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:
-------+-----+--------+------+-----+-----------------------+------+-----+ | name | bid | person | bid | rid | domain | rid | did | -------+-----+--------+------+-----+-----------------------+------+-----+ | 404 | 1 | Graham | 1 | 101 | www.grahamellis.co.uk | 101 | 201 | | 404 | 1 | Graham | 1 | 101 | www.sheepbingo.co.uk | 101 | 202 | -------+-----+--------+------+-----+-----------------------+------+-----+
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:
-------+-----+--------+------+------+-----------------------+------+------+ | name | bid | person | bid | rid | domain | rid | did | -------+-----+--------+------+------+-----------------------+------+------+ | 404 | 1 | Graham | 1 | 101 | www.grahamellis.co.uk | 101 | 201 | | 404 | 1 | Graham | 1 | 101 | www.sheepbingo.co.uk | 101 | 202 | | 404 | 1 | Lisa | 1 | 102 | NULL | NULL | NULL | | 405 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | -------+-----+--------+------+------+-----------------------+------+------+
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.
THREE WAY JOINS - LOOKING FOR INCOMPLETE RECORDS
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;
-------+-----+--------+------+------+--------+------+------+ | name | bid | person | bid | rid | domain | rid | did | -------+-----+--------+------+------+--------+------+------+ | 404 | 1 | Lisa | 1 | 102 | NULL | NULL | NULL | | 405 | 2 | NULL | NULL | NULL | NULL | NULL | 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;
-------+-----+--------+------+------+--------+------+------+ | name | bid | person | bid | rid | domain | rid | did | -------+-----+--------+------+------+--------+------+------+ | 405 | 2 | NULL | NULL | NULL | NULL | NULL | 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;
-------+-----+--------+------+------+--------+------+------+ | name | bid | person | bid | rid | domain | rid | did | -------+-----+--------+------+------+--------+------+------+ | 404 | 1 | Lisa | 1 | 102 | NULL | NULL | NULL | -------+-----+--------+------+------+--------+------+------+
SUMMARY OF MYSQL COMMANDS USED
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:
DROP TABLE IF EXISTS bdg;
DROP TABLE IF EXISTS res;
DROP TABLE IF EXISTS dom;
CREATE TABLE bdg (name text, bid INT PRIMARY KEY);
CREATE TABLE res (person text, bid INT, rid INT PRIMARY KEY);
CREATE TABLE dom (DOMAIN text, rid INT, did INT PRIMARY KEY);
INSERT INTO bdg VALUES ("404",1);
INSERT INTO res VALUES ("Graham",1,101);
INSERT INTO dom VALUES ("www.grahamellis.co.uk",101,201);
INSERT INTO dom VALUES ("www.sheepbingo.co.uk",101,202);
INSERT INTO res VALUES ("Lisa",1,102);
INSERT INTO bdg VALUES ("405",2);
SELECT * FROM bdg;
SELECT * FROM res;
SELECT * FROM dom;
SELECT * FROM bdg, res WHERE bdg.bid = res.bid ;
SELECT * FROM bdg, res, dom WHERE bdg.bid = res.bid AND
res.rid = dom.rid;
SELECT * FROM bdg LEFT JOIN res ON bdg.bid = res.bid ;
SELECT * FROM (bdg LEFT JOIN res ON bdg.bid = res.bid)
LEFT JOIN dom ON res.rid = dom.rid;
SELECT * FROM (bdg LEFT JOIN res ON bdg.bid = res.bid)
LEFT JOIN dom ON res.rid = dom.rid WHERE dom.rid IS NULL;
SELECT * FROM (bdg LEFT JOIN res ON bdg.bid = res.bid)
LEFT JOIN dom ON res.rid = dom.rid WHERE res.rid IS NULL;
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;