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;
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!
Share with friends