SQL - Join

SQL JOIN joins together two tables on a matching table column, ultimately forming one single temporary table. The key word here is temporary. The tables themselves remain intact, and running a JOIN query does not in any way change the data or table structure. JOIN is another way to select specific data from two or more relational tables.


In order to perform a JOIN query, we need a few pieces of information: the name of the table and table column we want to join on and a condition to meet for the JOIN to happen. This should sound a little confusing as there is much going on in a JOIN query, so let's take a look at an example:

SQL Join Query Code:

USE mydatabase;

SELECT *
FROM orders
JOIN inventory
ON orders.product = inventory.product;

SQL Join Results:

id	customer	day_of_order	product	quantity	id	product	quantity	price
1	Tia	2008-08-01 00:00:00.000	Hanging Files	11	5	Hanging Files	33	14.99
2	Tia	2008-08-01 00:00:00.000	Stapler	3	4	Stapler	3	7.99
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14	5	Hanging Files	33	14.99
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
6	Tia	2008-07-25 00:00:00.000	HP Printer	4	2	HP Printer	9	89.99

The line beginning with JOIN (Line 4) is where we tell SQL which table we would like to join. The next line (Line 5) is a different story. Here is where we have specified the condition to JOIN ON. In this case, both tables have identical product columns which makes them an ideal target for a join. Basically we are temporarily merging the tables connecting them where they match, the product column.
This type of join matches values from one table column with a corresponding value in another table and uses that match to merge the tables together. In our make-believe store world, this let's us join the inventory table with the orders table to show us all the items we currently have in stock for our customers and also the price of each item.
Let's rework this query a bit and strip away a few of the table columns to make our results easier to read and understand. We will replace the (*) parameter with a list containing only the table columns we are interested in viewing.

SQL Join:

USE mydatabase;

SELECT orders.customer,
orders.day_of_order,
orders.product,
orders.quantity as number_ordered,
inventory.quantity as number_instock,
inventory.price
FROM orders
JOIN inventory
ON orders.product = inventory.product

SQL Results:

customer	day_of_order	product	number_ordered	number_instock	price
Tia	2008-08-01 00:00:00.000	Hanging Files	11	33	14.99
Tia	2008-08-01 00:00:00.000	Stapler	3	3	7.99
A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14	33	14.99
Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5	25	179.99
Tia	2008-07-25 00:00:00.000	19" LCD Screen	5	25	179.99
Tia	2008-07-25 00:00:00.000	HP Printer	4	9	89.99

Since we have one column in each table named the same thing (quantity), we used AS to modify how these columns would be named when our results were returned. These results should be more satisfying and easier to read now that we have removed some of the unnecessary columns.

SQL - Right Join

RIGHT JOIN is another method of JOIN we can use to join together tables, but its behavior is slightly different. We still need to join the tables together based on a conditional statement. The difference is that instead of returning ONLY rows where a join occurs, SQL will list EVERY row that exists on the right side, (The JOINED table).

SQL - Right Join:

USE mydatabase;

SELECT *
FROM orders
RIGHT JOIN inventory
ON orders.product = inventory.product

SQL Results:

id	customer	day_of_order	product	quantity	id	product	quantity	price
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	5	1	19" LCD Screen	25	179.99
6	Tia	2008-07-25 00:00:00.000	HP Printer	4	2	HP Printer	9	89.99
NULL	NULL	NULL	NULL	NULL	3	Pen	78	0.99
2	Tia	2008-08-01 00:00:00.000	Stapler	3	4	Stapler	3	7.99
1	Tia	2008-08-01 00:00:00.000	Hanging Files	11	5	Hanging Files	33	14.99
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	14	5	Hanging Files	33	14.99
NULL	NULL	NULL	NULL	NULL	6	Laptop	16	499.99

You should see a new row at the bottom of the results box with a bunch of NULL values. This is a result of the RIGHT JOIN and is the intended result from running the query. We end up with an extra row because inside of the inventory table, the Laptop item was not joined with a product from the orders table. This just means that we have not sold a laptop as of yet and it shouldn't be much a surprise since we already know from querying the orders table in previous lessons that there have been no laptop orders so far.
By specifying RIGHT JOIN, we have told SQL to join together the tables even if no matches are found in the conditional statement. All records that exist in the table on the right side of the conditional statement (ON orders.product = inventory.product) will be returned and NULL values will be placed on the left if no matches are found.

SQL - Left Join

SQL LEFT JOIN works exactly the same way as RIGHT JOIN except that they are opposites. NULL values will appear on the right instead of the left and all rows from the table on the left hand side of the conditional will be returned.
Unfortunately, we will not be able to show a very intuitive example of a LEFT JOIN because of how our tables are structured. The orders table should always have a matching inventory item and if not, that means we are in big trouble as we could be selling items we do not carry in inventory. For good measure, here's what a LEFT JOIN would look like:

SQL Left Join:

USE mydatabase;

SELECT *
FROM orders
LEFT JOIN inventory
ON orders.product = inventory.product

SQL JOIN is intended to bring together data from two tables to form a single larger table, and often, it will paint a more detailed picture of what the data represents. By merging these two data sets, we were able to peer into our database and ensure that each item ordered so far is in stock and ready to be shipped to our customers.

Posted in: SQL