Oracle 5.0, Reference Manual

Page: 1111 / 3232

SELECT
Syntax
1091
• If there is no matching row for the right table in the
ON
or
USING
part in a
LEFT JOIN
, a row with all
columns set to
NULL
is used for the right table. You can use this fact to find rows in a table that have
no counterpart in another table:
SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;
This example finds all rows in
left_tbl
with an
id
value that is not present in
right_tbl
(that is,
all rows in
left_tbl
with no corresponding row in
right_tbl
). This assumes that
right_tbl.id
is declared
NOT NULL
. See Section 8.3.1.7, “
LEFT JOIN
and
RIGHT JOIN
Optimization” .
• The
USING(column_list)
clause names a list of columns that must exist in both tables. If tables
a
and
b
both contain columns
c1
,
c2
, and
c3
, the following join compares corresponding columns from
the two tables:
a LEFT JOIN b USING (c1,c2,c3)
• The
NATURAL [LEFT] JOIN
of two tables is defined to be semantically equivalent to an
INNER
JOIN
or a
LEFT JOIN
with a
USING
clause that names all columns that exist in both tables.
•
RIGHT JOIN
works analogously to
LEFT JOIN
. To keep code portable across databases, it is
recommended that you use
LEFT JOIN
instead of
RIGHT JOIN
.
• The
{ OJ ... }
syntax shown in the join syntax description exists only for compatibility with
ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used
elsewhere in syntax descriptions.
SELECT left_tbl.*
FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id }
WHERE right_tbl.id IS NULL;
•
STRAIGHT_JOIN
is similar to
JOIN
, except that the left table is always read before the right table.
This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
Some join examples:
SELECT * FROM table1, table2;
SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 USING (id);
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;
Join Processing Changes in MySQL 5.0.12
Beginning with MySQL 5.0.12, natural joins and joins with
USING
, including outer join variants, are
processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of
MySQL with respect to
NATURAL JOIN
and
JOIN ... USING
according to SQL:2003. However,
these changes in join processing can result in different output columns for some joins. Also, some
queries that appeared to work correctly in older versions must be rewritten to comply with the standard.
These changes have five main aspects:
• The way that MySQL determines the result columns of
NATURAL
or
USING
join operations (and thus
the result of the entire
FROM
clause).
• Expansion of
SELECT *
and
SELECT tbl_name.*
into a list of selected columns.
• Resolution of column names in
NATURAL
or
USING
joins.