Joins

Episode #7 of the course Intro to SQL by Mike Schmitt

 

What if we need to combine or link data from two different tables? Say a small business makes a sale and needs to pull merchandise info from our Inventory table to create an order record. We can use a JOIN to pull data from two or more tables using a common field to link them. There are multiple types of JOINs available, and for our example, we will use an INNER JOIN. This type of JOIN returns all rows from the linked tables where the matching criteria is met. We will join the Orders table to our Inventory table, and the Orders table is structured like so:

Recall that this is what is contained in our Inventory table:

And here is the relationship model between the two tables showing the field in common that we can use to link:

The InventoryID field is shared by both tables, and we will use this to link. Let’s say we want to pull the merchandise Type, Brand, Color, and Size for all orders along with the CartCount and OrderDate. Here is the SQL syntax, which we will look at in further detail:

SELECT Inventory.Type, Inventory.Brand, Inventory.Color, Inventory.Size
 , Orders.CartCount, Orders.OrderDate
 FROM Orders INNER JOIN Inventory ON Orders.InventoryID=Inventory.InventoryID;

The SELECT portion looks somewhat familiar, but you’ll notice that each column name has the table name as a prefix. This indicates which table to pull the column from. For instance, Inventory.Type indicates we want to use the Type field from the Inventory table in our column selection. After the SELECT comes the JOIN syntax. We are saying take table Orders and INNER JOIN table Inventory. The column to match on, InventoryID in this case, follows the ON syntax. We are matching on InventoryID, so we write this as Orders.InventoryID=Inventory.InventoryID, again listing the table prefix to indicate which field to pull each column from.

Here is the result set once the query is executed:

This looks pretty similar to many online order confirmations!

Note that there are a few additional types of JOINs available to you that we won’t get to but are quite useful:

LEFT JOIN: Return all rows from left table plus any matched rows from right table.
RIGHT JOIN: Return all rows from right table plus any matched rows from left table.
FULL JOIN: Return all rows when there is a match in either table.

And now you know how to link tables!

Explore on your own with an SQL Fiddle of today’s lesson!

 

Recommended book:

“SQL QuickStart Guide: The Simplified Beginner’s Guide To SQL” by ClydeBank

 

Share with friends