Databases Reference
In-Depth Information
Join Syntax: Out with the Old and In with
the New (SQL:1999)
Not only can you
two or more tables in a number of different ways, but you can
also use two different syntax forms to perform these joins. As of Oracle9
join
join
To combine two or more tables in a query
to produce rows as a result of a compar-
ison between columns in the tables.
i
, the full
ANSI SQL:1999 standard for join syntax is supported. Prior to Oracle9
, Oracle
used a proprietary syntax that wasn't always compatible with the ANSI standard.
Oracle's proprietary syntax, which is still supported in Oracle9
i
i
and Oracle
10
for backward compatibility with existing code, put all of the join conditions
in the
g
clause. It also relied on relatively obscure meth-
ods to indicate certain types of join operations. The newer syntax relies more
heavily on concise yet descriptive keywords to clearly indicate what operation is
being performed. We'll cover both the old and new syntax in this chapter; as a
DBA or developer, you'll most likely see new applications using the new syntax
and plenty of existing applications that use the old syntax.
SELECT
statement's
WHERE
All new SQL code should use the SQL:1999 or SQL:2003 standard syntax for read-
ability and cross-platform compatibility.
There is no performance benefit to using one syntax over the other; the same
kind of join using either syntax will translate into the same internal SQL engine
operation. One of the biggest benefits is the ease with which the new syntax can
be written and understood. The join conditions are now separated from the
WHERE
clause, if one even exists,
ends up being much cleaner because it's used only for filtering the rows being
returned from the query, instead of being intertwined with table join conditions.
In each section of this chapter, you'll see how the database analyst, Janice,
uses both formats for each new query she develops for the boss.
clause and placed in the
FROM
clause. The
WHERE
Equijoins
Equijoins
. Given two or
more tables, an equijoin will return the results of these tables where a common
column between any given pair of tables has the same value (an equal value).
Equijoins are typically joins between foreign keys in one table to a primary key
in another table.
are also commonly known as simple joins, or
inner joins
equijoin
A join between two tables where rows are
returned if one or more columns in com-
mon between the two tables are equal
and not
NULL
.
Pre-Oracle9
i
Equijoin Syntax
The boss, King, gets his employee report with only the department ID on it,
because the query used for the report is based on only the
inner join
See
equijoin
.
table.
When the company was smaller, he knew automatically that department 100
was the Finance department, and so on. But now, with almost 30 departments in
EMPLOYEES
