NOTE:
For demos in this article, we will use three tables: Customer, SalesOrder and OrderLine.
The structure of the Customer table is:
Name | Data Type |
CustID | int |
FirstName | nvarchar(50) |
LastName | nvarchar(50) |
StreetAddress | nvarchar(255) |
City | nvarchar(255) |
State | char(2) |
ZipCode | nvarchar(10) |
The Customer table contains the following data.
CustID | FirstName | LastName | StreetAddress | City | State | ZipCode |
1 | David | Giard | 123 Oxford Ct | Erlanger | KY | 40111 |
2 | Magic | Johnson | 456 Hollywood Blvd | Lansing | MI | 45222 |
3 | Bubba | Smith | 789 Killbubbakill St | Baltimore | MD | 10111 |
The structure of the SalesOrder table is
Name | Data Type |
OrderID | int |
CustID | int |
OrderDate | datetime |
The SalesOrder table contains the following data.
OrderID | CustID | OrderDate |
1 | 1 | 2009-03-01 |
2 | 1 | 2009-03-02 |
3 | 2 | 2009-03-07 |
4 | 2 | 2009-03-14 |
5 | 3 | 2009-03-21 |
The structure of the OrderLine table is
Name | Data Type |
OrderID | int |
LineNumber | int |
ProductName | nvarchar(255) |
Quantity | int |
The OrderLine table contains the following data
OrderID | LineNumber | ProductName | Quantity |
1 | 1 | Widget | 7 |
1 | 2 | Super Widget | 4 |
2 | 1 | Widget | 5 |
2 | 2 | Super Widget | 3 |
3 | 1 | Widget | 2 |
4 | 1 | Super Widget | 3 |
5 | 1 | Widget | 6 |
5 | 2 | Super Widget | 1 |
In a previous article, I explained how we can (and often should) split a table into multiple tables in order to eliminate data redundancy - a process known as "normalization".
In this article, I'll explain how to retrieve related data from multiple tables and return them in a single result set.
Recall from the Normalization article that - in order to relate to tables - we add a key to each table. The Primary key in the parent table is a column that is unique for each row and, therefore, servers to uniquely identify a row. The child table contains a foreign key which is the same value as a Primary key in the parent table, so it points to a given row in the parent.
To retrieve data from multiple tables into a single result set, we do something called a "JOIN". In SQL, there are two ways to JOIN tables:
- Using the JOIN keyword
- Adding the join condition on a WHERE clause
JOIN keyword
The syntax for joining tables with the JOIN keyword is
The syntax for joining tables with the JOIN keyword is
SELECT [List of Columns]
FROM [Table 1]
JOIN [Table 2]
ON [Join Condition]
For example, to retrieve the Name, SalesOrder Date and SalesOrder Amount of each customer in our sample tables, use the following query:
SELECT
FirstName,
LastName,
OrderDate
FROM Customer
JOIN SalesOrder
ON Customer.CustID = SalesOrder.CustID
Notice that we need to prefix the CustID column name with the table name in our filter condition. This is because the CustID column name is not unique. We need to tell SQL to which column we are referring.
The results of this query are
FirstName | LastName | OrderDate |
David | Giard | 2009-03-01 |
David | Giard | 2009-03-02 |
David | Giard | 2009-03-07 |
Magic | Johnson | 2009-03-14 |
Bubba | Smith | 2009-03-21 |
WHERE clause
The syntax for joining two tables with the WHERE clause is
SELECT [List of Columns]
FROM [Table 1], [Table 2]
WHERE [Join Condition]
The syntax to return the same result set as above is
SELECT
FirstName,
LastName,
OrderDate
FROM Customer, SalesOrder
WHERE Customer.CustID = SalesOrder.CustID
Recall that the WHERE clause is also used to filter your result set. In fact, you can use it for both filtering and joining. The following two queries yield the same results (showing only those records that match customer 1.
SELECT
FirstName,
LastName,
OrderDate
FROM Customer
JOIN SalesOrder
ON Customer.CustID = SalesOrder.CustID
WHERE Customer.CustID = 1
SELECT
FirstName,
LastName,
OrderDate
FROM Customer, SalesOrder
WHERE Customer.CustID = SalesOrder.CustID
AND Customer.CustID = 1
Here is the result set for either of the above two queries:
FirstName | LastName | OrderDate |
David | Giard | 2009-03-01 |
David | Giard | 2009-03-02 |
David | Giard | 2009-03-07 |
You can use these same techniques to join more than two tables. Here is the syntax to add the OrderLine table to our queries
SELECT
SalesOrder.OrderID,
FirstName,
LastName,
OrderDate,
ProductName,
Quantity
FROM Customer
JOIN SalesOrder
ON Customer.CustID = SalesOrder.CustID
JOIN OrderLine
ON SalesOrder.OrderID = OrderLine.OrderID
SELECT
SalesOrder.OrderID,
FirstName,
LastName,
OrderDate,
ProductName,
Quantity
FROM Customer, SalesOrder, OrderLine
WHERE Customer.CustID = SalesOrder.CustID
AND SalesOrder.OrderID = OrderLine.OrderID
Here is the result set of either of these 3-table queries
OrderID | FirstName | LastName | OrderDate | ProductName | Quantity |
1 | David | Giard | 2009-03-01 | Widget | 7 |
1 | David | Giard | 2009-03-01 | Super Widget | 4 |
2 | David | Giard | 2009-03-02 | Widget | 5 |
2 | David | Giard | 2009-03-02 | Super Widget | 3 |
3 | David | Giard | 2009-03-07 | Widget | 2 |
4 | Magic | Johnson | 2009-03-14 | Super Widget | 3 |
5 | Bubba | Smith | 2009-03-21 | Widget | 6 |
5 | Bubba | Smith | 2009-03-21 | Super Widget | 1 |
I prefer to use the JOIN keyword syntax when joining tables together because it is more clear what part of the query is a filter and what part of a query is a join.
In this article, we showed the ways to use SQL Server to join multiple tables into a single result set.