In our last SQL tutorial, we talked about how to join tables using inner join doesn’t give you the result sets you need. Let’s take a look at this example. Here are two tables.
Let’s say you want to find out the availability of all products.
Consider this SQL statement:
SELECT a.ProductID, Description, Store, Quantity FROM PRODUCTS a JOIN PRODUCT_AVAILABILITY b ON a.ProductID = b.ProductID;
You will get:
Notice that one product is missing from the result set?
Product 9000A is missing because this product ID is not in the PRODUCT_AVAILABILITY table. When you join these two tables using an INNER JOIN (or just JOIN), MySQL automatically ignores keys that don’t exist in both.
How do you get all products to show up in your result set, whether they exist in both tables or not? You’ll have to use a OUTER JOIN.
Now, consider this SQL statement:
SELECT a.ProductID, Description, Store, Quantity FROM PRODUCTS a LEFT OUTER JOIN PRODUCT_AVAILABILITY b ON a.ProductID = b.ProductID;
A LEFT OUTER JOIN tells MySQL to include ALL records in the PRODUCTS table (the table on the left side of the JOIN), regardless of their existence in PRODUCT_AVAILABILITY.
Another type of OUTER JOIN is the RIGHT OUTER JOIN. It works like the LEFT OUTER JOIN except that it includes all records in the table on the right side of the JOIN, regardless of their existence in the other table.
Let’s switch the tables around and use RIGHT OUTER JOIN.
SELECT b.ProductID, Description, Store, Quantity FROM PRODUCT_AVAILABILITY a RIGHT OUTER JOIN PRODUCTS b ON a.ProductID = b.ProductID;
What if you have a situation where some records are in the left table but not in the right table, and at the same time some records are in the right table but not in the left table?
Some databases allow you to use something call FULL OUTER JOIN, which is basically a combination of LEFT OUTER JOIN and RIGHT OUTER JOIN. Unfortunately, FULL OUTER JOIN doesn’t exist in MySQL. In order to achieve what a FULL OUTER JOIN does in MySQL, you’ll have to use UNION to join two result sets. Here’s how you do it:
SELECT column1, column2, column3 FROM tableA LEFT OUTER JOIN tableB ON tableA.ID = tableB.ID UNION SELECT column1, column2, column3 FROM tableA RIGHT OUTER JOIN tableB
ON tableA.ID = tableB.ID;
UNION allows you to join two result sets from two SQL statements. Please note that the column names in the SELECT clause must be identical for UNION to work.
Originally published at www.ilearnsql.com.
