# Saturday, 21 March 2009
Back To Basics

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.