Qualifying Column Names

The only time it is actually necessary to qualify a column name is when that column is contained in both tables. This query will run successfully because the unqualified names are in only one of the tables:

select   title, qty
from     titles   t,   sales   s
where     t.title_id   =   s.title_id

title                                              qty
-------------------------------------------       -----
The Busy Executive’s Database Guide                5
The Busy Executive’s Database Guide                10
[...]
Fifty Years in Buckingham Palace Kitchens          20
Sushi, Anyone?                                     20

(21 row(s) affected)

Once you are familiar with your data, it is tempting to leave unique columns unqualified, and it is common practice to do so.

---

Tip:  Speaking as a consultant who is frequently called on to examine unfamiliar SQL code, I strongly recommend qualifying all column names. It can be a drag to do that on long, complicated queries, but on those queries especially, it is very helpful for someone who is not familiar with the data to see, at a glance, where each column originates. It will help new members of your team, and it will help you if you are ever away from your data for more than a couple of weeks. It causes no performance penalty and takes only a moment.

---

Primary and Foreign Keys

Writing queries that join tables effectively requires you to be familiar with the relationships between tables in your database. You know that a common key is the data that is shared between two tables. To find those keys, understanding which columns compose a table’s primary keys and foreign keys is useful information for creating joins.

A primary key is a column or set of columns that uniquely identifies each row in a table.

A foreign key is a column or set of columns that refers to a primary key in another table.

Not all tables have primary keys or foreign keys. In the titles table, the primary key is title_id. For each row in the table, there is a title_id that is unique to that row.

In the sales table, title_id is a foreign key, because it corresponds to the primary key in titles.

If you know a foreign key on a table, you also know a join key between that table and the table that it references.

Consider the publishers table. What is the primary key for publishers? (It is pub_id.) Some tables that contain a foreign key for the publishers table include the employee table, the titles table, and the pub_info table.

Primary keys may not be null. Foreign keys may be null, in which case the relationship is considered “undefined.” Null values never join to null values in other tables.

Composite (Multipart) Keys

Some tables need more than one column to form their primary keys. The sales table contains a foreign key column, stor_id, that refers to the primary key in stores. A store may make many orders, so there could be several rows with the same stor_id. Sales also contains an ord_num column, but a store ordering more than one title may place the order under the same order number. To formulate a unique value, three columns must be used: stor_id, ord_num, and title_id must all be used to guarantee the uniqueness of a row.

In this example, there are no foreign keys in the sales table. In the pubs database, there are no tables that contain composite join keys. But, in the real world, you will run into this problem. If you have two tables, t1 and t2, with a composite join key on id1 and id2, here is what you would need to provide to join the two tables. You must specify all of the join keys for the result data to be meaningful:

/* OLD STYLE */
select   t1.c1, t2.c1
from     t1, t2
where    t1.id1 = t2.id1
         and t1.id2 = t2.id2

/* ANSI STYLE */
select  t1.c1, t2.c1
from    t1 join t2 on t1.id1 = t2.id1 and t1.id2 = t2.id2

Cross Joins or Cartesian Products

Speaking of meaningful data, what would happen if you used the old style join method and forgot to include a join key? The answer is, “a Cartesian product.” This occurs when every row in one table is joined to every row in the other table. It is called a Cartesian product because the result set will contain exactly (rows in t1) * (rows in t2) rows.

This is no big deal for little tables, like titles and sales.

/* Oh No! It’s a CROSS JOIN! */
select    t.title_id,     s.qty
from      sales s,     titles  t

title_id    qty
-------    -----
BU1032     5
BU1032     3
BU1032     50
BU1032     75
BU1032     10
[...]
TC7777     15
TC7777     10
TC7777     25
TC7777     30

(378 row(s) affected)

Because I didn’t specify a relationship between these two tables, I got a cross join, joining each row in sales to every row in titles. It’s only 378 rows in this database, but in bigpubs, where titles has 537 rows and sales has 168,725 rows, a Cartesian product would produce 90,605,325 rows!

It’s quite rare to see a cross join that someone actually wanted. Creating them by accident isn’t a problem with the ANSI style join.

select   t.title_id,    s.qty
from     titles   t   inner  join   sales   s

Msg 170, Level 15, State 1
Line 2: Incorrect syntax near ‘s’.

I forgot the ON clause that specifies the common key between titles and sales. The server gave me a syntax error instantly, instead of a Cartesian product in considerably more time.

If you really do want a Cartesian product, use the term cross join with the ANSI syntax

select  t.title_id, s.qty
from    titles t cross join sales s