Week 1

Day 7
Joining Tables

Relational database systems get their name from the relationships between tables. To exploit these relationships, you need a way to draw information from more than one table. To draw information from more than one table, you use table joins.

Table joins are the topic of the day. Understanding joins is central to your understanding of T-SQL, so today’s lesson is about as important a lesson as there is in this book. These are the topics that are covered today:

•  Understanding the join

•  ANSI syntax and T-SQL syntax for the join

•  Primary and foreign keys

•  The cross join (Cartesian product)

•  How to read and understand an entity relationship diagram

•  Displaying data from multiple tables

•  Joining multiple (more than two) tables

Understanding the Join

Starting with MS SQL Server 6.5, there are two different ways to specify a join: the ANSI-92 compliant way and the T-SQL way. Because both are now T-SQL syntax, from now on I call them ANSI style and old style. Throughout this chapter, I provide two queries that do the same thing: the first using old style, the second using ANSI style. I’ll talk a little later about the differences between these two styles.

To join two tables together, the tables must have a relationship. This relationship is indicated by a common key.

A common key, also called a join key, is the column (or columns) shared by two tables that allows them to be meaningfully joined together.

Usually, this relationship is logically defined, not physically defined. This means that the server doesn’t know anything about the relationships between tables, and you need to specify those relationships when you submit your query. It’s time for an example.

Both the sales table and the titles table contain a column called title_id (see Figure 7.1). Column title_id is the common key shared by these two tables. Figure 7.1 shows how the server reads each table and joins together rows sharing a common key (title_id).

Figure 7.1.  Rows from titles are joined to rows from the sales table.

Here is the query that performs a join, as shown in Figure 7.1. (I’ve converted the title to a char(20) so that it will fit on the page):

/* OLD STYLE */
select convert(char(20), titles.title) Title, sales.ord_date, sales.qty
from   titles, sales
where  titles.title_id  =  sales.title_id

/* ANSI STYLE */
select convert(char(20), titles.title) Title, sales.ord_date, sales.qty
from   titles inner join sales on titles.title_id = sales.sales_id

Title                        ord_date                 qty
-------------          ----------------              ------
The Busy Executive’s Sep 14 1994 12:00AM              5
The Busy Executive’s Sep 14 1994 12:00AM              10
Cooking with Compute Mar 11 1993 12:00AM              25
You Can Combat Compu Feb 21 1993 12:00AM              35
Straight Talk About  Oct 28 1993 12:00AM              15
Silicon Valley Gastr Dec 12 1993 12:00AM              10
The Gourmet Microwav Sep 14 1994 12:00AM              25
The Gourmet Microwav Sep 14 1994 12:00AM              15
But Is It User Frien May 22 1993 12:00AM              30
Secrets of Silicon V May 24 1993 12:00AM              50
Computer Phobic AND  May 29 1993 12:00AM              20
Is Anger the Enemy?  Sep 13 1994 12:00AM              3
Is Anger the Enemy?  Sep 13 1994 12:00AM              75
Is Anger the Enemy?  Sep 14 1994 12:00AM              10
Is Anger the Enemy?  Sep 14 1994 12:00AM              20
Life Without Fear    May 29 1993 12:00AM              25
Prolonged Data Depri May 29 1993 12:00AM              15
Emotional Security:  May 29 1993 12:00AM              25
Onions, Leeks, and G Jun 15 1992 12:00AM              40
Fifty Years in Bucki Jun 15 1992 12:00AM              20
Sushi, Anyone?       Jun 15 1992 12:00AM              20
(21    row(s)   affected)

In the SELECT list, columns are identified by specifying <table name>.<column name>, as in sales.qty. The JOIN clauses are specified either in the FROM clause (ANSI) or in the WHERE clause (old). (I explain more about syntax in the next section of this chapter.)

In the titles table, the title_id uniquely identifies each row in the table. In the sales table, each book order has a title_id associated with it to indicate which book was ordered. There may be zero corresponding rows in sales, as in the case of book MC3026 (The Psychology of...); there may be one corresponding row, as in the case of books BU1111 (Cooking with Computers) and BU2075 (Combat Computer Stress); or there may be many corresponding rows, as with BU1032 (Busy Executive’s...). Let’s look at what happens in each of those three cases:

•  One row in titles, one row in sales. This one is easy. The row in titles joins to the row in sales, and information from the two rows is displayed in the result set.

•  One row in titles, no rows in sales. When there are no corresponding rows in a joined table, the row is not included in the result set. When you join tables, you are asking for data where a correspondence exists between the two tables. Because no correspondence exists, you receive no information from either table on that row. To get information on sales for books with no sales (the quantity would be null), you would need to use an outer join. Outer joins are discussed on the last day.

•  One row in titles, many rows in sales. The Busy Executive has two rows in sales for two different order numbers. When one row in a table joins to many rows in another table, the information from the first table is repeated for every corresponding row in the other table.