31 Oct Sql server tutorial 11 Accessing Multiple Tables
Key Skills & Concepts
- Perform Basic Join Operations
- Join Tables with Shared Column Names
- Use the Condition Join
- Perform Union Operations
In important component of any relational database is the relationship that can exist
between any two tables. This relationship allows you to tie data in one table to data in
another table. These sorts of relationships are particularly useful when you want to query
related data from more than one table and you want to retrieve that data in a meaningful
way so that the relationships between the tables are, for all practical purposes, invisible.
One method that SQL supports for querying data in this manner is to join the tables in one
statement. A join is an operation that matches rows in one table with rows in another so
that columns from both tables may be placed side by side in the query results as if they all
came from a single table. SQL defines several types of join operations. The type you can
use in any given situation depends on your SQL implementation (with regard to
statements supported and how performance might be impacted), which data you want
returned, and how the tables have been defined. In this chapter, I discuss a number of
operations that combine data from multiple tables, including joins and unions, and provide
details about how they’re implemented and the results you can expect when you use them.
Perform Basic Join Operations
One of the simplest types of joins to implement is the comma-separated join. In this type
of operation, you’re required only to supply a list of tables (separated by commas) in the
FROM clause of the SELECT statement. You can, of course, qualify the join in the
WHERE clause—which you would want to do to obtain meaningful data from the tables
—but you’re not required to do so. However, before I discuss the WHERE clause, let’s
first take a look at the comma-separated join at its most basic.
Suppose you want to display data from the CD_INVENTORY table and the
PERFORMERS table, shown in Figure 11-1. (The figure also includes the PERF_TYPE
table, which we’ll be using in the “Creating Joins with More than Two Tables” section.)
You can view the data in the CD_INVENTORY and PERFORMERS tables by querying
each table separately, or you can join the tables in one statement.
Actually, the preceding SELECT returns far more rows than are shown here. These
results represent only a partial list. Because the CD_INVENTORY table contains 11 rows
and the PERFORMERS table contains 10 rows, the entire query results would contain 110
rows. Let’s take a closer look at this. The Drive All Night row in the CD_INVENTORY
table has been joined with each row in the PERFORMERS table, which totals 10 rows.
Each of the remaining 10 rows in the CD_INVENTORY table is matched to each row in
the PERFORMERS table in the same way. As a result, there are 110 rows (11 × 10 = 110).
The data returned by this query is now a lot more meaningful. Each CD is matched
with the appropriate performer, and only 11 rows are displayed, rather than 110. However,
even these query results include repetitive data (the PERF_ID column). In addition, you
might find that not only do you want to eliminate duplicate columns, but you also want to
display only certain columns and perhaps qualify your search condition even further.
Aside from these guidelines, you’re free to create whatever sort of SELECT statement
is necessary to extract the information you need from the participating tables. And while
using the WHERE clause to specify the join condition was the original way to do joins in
SQL, later in this chapter you will see that there are now other syntax variations using the
JOIN keyword, which most SQL programmers prefer over the original syntax. But no
matter what syntax you use, always keep in mind that there needs to be some sort of
logical connection between the tables. This connection is often seen in the form of a
foreign key, but that doesn’t have to be the case. (For more information about foreign
keys, see Chapter 4.)
Using Correlation Names
As I stated earlier, you must qualify your column references by adding table names to
those columns that share a name. However, as a general policy, it’s a good idea to always
qualify column references when joining tables, whether or not it’s necessary. This makes
referencing the code at a later time much easier if the statement is fully self-documented.
However, as your queries become more complex, it can become increasingly tedious to
reenter table names every time you reference a column. Because of this, SQL supports
correlation names, or aliases, that can be used for the duration of a statement. A
correlation name is simply a shortened version of the actual table name that is used to