Database Reference
In-Depth Information
Joining Tables
The
JOIN
clause linkstwo tables together in a
SELECT
,
UPDATE
, or
DELETE
statement.
JOIN
links tablesbased on columns with common data for purposes of selecting, updating,
or deleting data. In
A Little Complexity
, for instance, we joined two tables named
books
and
status_names
, taking advantage of the design that put identical values in the
status
column of
books
and the
status_id
column of
status_names
. That way,
we could show data from each table about the same book:
SELECT
book_id
,
title
,
status_name
FROM
books
JOIN
status_names
WHERE
status
=
status_id
;
Let's review the way a join works, using this example. The
status
and
status_id
fields both contain numbers that refer to a status. In the
books
table, the numbers have no
intrinsic meaning. But the
status_names
table associates the numbers with meaningful
text. Thus, by joining the tables, you can associate a book with its status.
Sometimes there are alternatives to the
JOIN
clause. For instance, when constructing an
SQL statement that includes multiple tables, a simple method is to list the tables in a
comma-separated list in the appropriate position of the SQL statement — for a
SELECT
statement, you would list them in the
FROM
clause — and toprovide pairing of columns in
the
WHERE
clause on which the tables will be joined. This is the method we have used sev-
eral times in the previous chapters. Although this method works fine and would seem fairly
straightforward, a more agreeable method is to use a
JOIN
clause to join both tables and to
specify the join point columns. When you have an error with an SQL statement, keeping
these items together and not having part of them in the
WHERE
clause makes troubleshoot-
ing SQL statements easier.
With
JOIN
, tables are linked together based on columns with common data for purposes of
selecting, updating, or deleting data. The
JOIN
clause is entered in the relevant statement
where tables referenced are specified usually. This precludes the need to join the tables
based on key columns in the
WHERE
clause. The
ON
operatoris used to indicate the pair of
columns by which the tables are to be joined (indicated with the equals-sign operator). If
needed, you may specify multiple pairs of columns, separated by
AND
. If the column names
by which the two tables are joined are the same in both tables, as an alternative method, the
USING
operator may be given along with a comma-separated list of columns that both
tables have in common, contained within parentheses. The columns must be contained in
each table that is joined. To improve performance, join to a column that is indexed.
