MySQL Outer Joins
The students needed yet another example of LEFT JOIN, RIGHT JOIN, and FULL JOIN syntax (by combining a left and right join with the UNION set operator). To that end, I put this set of examples together.
The example also shows how to order the result set from a derived table with the UNION operator. It uses the WITH clause to build a Common Table Expression (CTE), which allows the query to order the UNION set operator’s product based on the left and right join queries. It uses a CASE statement to order the result sets. The left_table is the parent table and the right_table is the child table in the relationship, which means the right_table holds a left_id foreign key column that lets you connect matching rows in the left_table.
You build the little model with the following script:
-- ----------------------------------------------------------------- -- Drop the demonstration tables. -- ----------------------------------------------------------------- DROP TABLE IF EXISTS left_table, right_table; -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE left_table ( left_id int unsigned primary key auto_increment , leftstring varchar(10)); -- ----------------------------------------------------------------- -- Create left_table. -- ----------------------------------------------------------------- CREATE TABLE right_table ( right_id int unsigned primary key auto_increment , left_id int unsigned , rightstring varchar(10)); -- ----------------------------------------------------------------- -- Insert five rows to the left table, which holds a -- left_id primary key column. -- ----------------------------------------------------------------- INSERT INTO left_table (leftstring) values ('One'); INSERT INTO left_table (leftstring) values ('Two'); INSERT INTO left_table (leftstring) values ('Three'); INSERT INTO left_table (leftstring) values ('Four'); INSERT INTO left_table (leftstring) values ('Five'); -- ----------------------------------------------------------------- -- Delete row four to create a gap. -- ----------------------------------------------------------------- DELETE FROM left_table where left_id = 4; -- ----------------------------------------------------------------- -- Insert four rows, skipping a foreign key value for the -- left_id primary key value of 2. -- ----------------------------------------------------------------- INSERT INTO right_table (rightstring,left_id) values ('One',1); INSERT INTO right_table (rightstring,left_id) values ('Three',3); INSERT INTO right_table (rightstring,left_id) values ('Four',4); INSERT INTO right_table (rightstring,left_id) values ('Five',5); |
Here are the join statements:
INNER JOIN
The INNER JOIN only returns those rows that match between a primary and foreign key column or set of columns.
SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l INNER JOIN right_table r ON l.left_id = r.left_id; |
It produces the following result set:
+---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 3 | Three | 3 | 2 | Three | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 3 rows in set (0.00 sec) |
LEFT OUTER JOIN
The LEFT OUTER JOIN only returns those rows that match between a primary and foreign key column or set of columns and any rows in the table on the lefthand side of the join that fail to match with any row on the righthand side of the join. The non-matching rows are also known as the right complement of the join.
1 2 3 4 5 6 7 | SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id; |
It produces the following result set:
+---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 2 | Two | NULL | NULL | NULL | | 3 | Three | 3 | 2 | Three | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 4 rows in set (0.00 sec) |
Add the following line 8 to the query and you get only those rows in the lefthand table that have no child-related rows in the righthand table. These rows are sometimes called childless parent rows. More or less, the use case for this type of query is to find order headers without order lines.
6 7 8 | FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id WHERE r.left_id IS NULL; |
It produces the following result set:
+---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 2 | Two | NULL | NULL | NULL | +---------+------------+---------+----------+-------------+ 1 row in set (0.00 sec) |
RIGHT OUTER JOIN
1 2 3 4 5 6 7 | SELECT l.left_id , l.leftstring , r.left_id , r.right_id , r.rightstring FROM left_table l RIGHT JOIN right_table r ON l.left_id = r.left_id; |
It produces the following result set:
+---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | 1 | One | 1 | 1 | One | | 3 | Three | 3 | 2 | Three | | NULL | NULL | 4 | 3 | Four | | 5 | Five | 5 | 4 | Five | +---------+------------+---------+----------+-------------+ 4 rows in set (0.00 sec) |
Add the following line 8 to the query and you get only those rows in the righthand table that have no parent-related rows in the lefthand table. These rows are sometimes called orphans because they have no parent row. More or less, the use case for this type of query is to find latent order lines after deleting the order header.
6 7 8 | FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id WHERE l.left_id IS NULL; |
It produces the following result set:
+---------+------------+---------+----------+-------------+ | left_id | leftstring | left_id | right_id | rightstring | +---------+------------+---------+----------+-------------+ | NULL | NULL | 4 | 3 | Four | +---------+------------+---------+----------+-------------+ 1 row in set (0.00 sec) |
FULL OUTER JOIN
The full outer join doesn’t exist in MySQL, so you combine a LEFT OUTER JOIN and RIGHT OUTER JOIN with the UNION operator. The UNION operator eliminates the duplicate row from the intersection of the joins.
Here’s the full query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | WITH cte AS (SELECT l.left_id AS primary_left_id , l.leftstring , r.left_id AS foreign_left_id , r.right_id , r.rightstring FROM left_table l LEFT JOIN right_table r ON l.left_id = r.left_id UNION SELECT l.left_id AS primary_left_id , l.leftstring , r.left_id AS foreign_left_id , r.right_id , r.rightstring FROM left_table l RIGHT JOIN right_table r ON l.left_id = r.left_id) SELECT * FROM cte ORDER BY CASE WHEN 'One' IN (leftstring, rightstring) THEN 1 WHEN 'Two' IN (leftstring, rightstring) THEN 2 WHEN 'Three' IN (leftstring, rightstring) THEN 3 WHEN 'Four' IN (leftstring, rightstring) THEN 4 WHEN 'Five' IN (leftstring, rightstring) THEN 5 END; |
It produces the following result set:
+-----------------+------------+-----------------+----------+-------------+ | primary_left_id | leftstring | foreign_left_id | right_id | rightstring | +-----------------+------------+-----------------+----------+-------------+ | 1 | One | 1 | 1 | One | | 2 | Two | NULL | NULL | NULL | | 3 | Three | 3 | 2 | Three | | NULL | NULL | 4 | 3 | Four | | 5 | Five | 5 | 4 | Five | +-----------------+------------+-----------------+----------+-------------+ 5 rows in set (0.00 sec) |
Add the following lines 18 and 19 to the query and you get only those rows that are childless parent rows or orphaned child rows. More or less, the use case for this type of query is to find both order headers without order lines and order lines abandoned by deleted order headers.
17 18 19 | SELECT * FROM cte WHERE primary_left_id IS NULL OR foreign_left_id IS NULL |
It produces the following result set:
+-----------------+------------+-----------------+----------+-------------+ | primary_left_id | leftstring | foreign_left_id | right_id | rightstring | +-----------------+------------+-----------------+----------+-------------+ | 2 | Two | NULL | NULL | NULL | | NULL | NULL | 4 | 3 | Four | +-----------------+------------+-----------------+----------+-------------+ 2 rows in set (0.00 sec) |
The ORDER BY clause used is a variation on the more common choice of:
WHEN leftstring = 'One' OR rightstring = 'One' THEN 1 |
The position of two string literals in any statement is a bad idea. Inverting the literal on the right and using a IN operator gives you a better and clearer WHEN statement:
WHEN 'One' IN (leftstring, rightstring) THEN 1 |
As always, I hope this helps those looking for syntax examples.
Subscribe via RSS