Oracle 5.0, Reference Manual

Page: 1112 / 3232

SELECT
Syntax
1092
• Transformation of
NATURAL
or
USING
joins into
JOIN ... ON
.
• Resolution of column names in the
ON
condition of a
JOIN ... ON
.
The following list provides more detail about several effects of the 5.0.12 change in join processing.
The term “previously” means “prior to MySQL 5.0.12.”
• The columns of a
NATURAL
join or a
USING
join may be different from previously. Specifically,
redundant output columns no longer appear, and the order of columns for
SELECT *
expansion may
be different from before.
Consider this set of statements:
CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);
Previously, the statements produced this output:
+------+------+------+------+
| i | j | k | j |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
+------+------+------+------+
+------+------+------+------+
| i | j | k | j |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
+------+------+------+------+
In the first
SELECT
statement, column
j
appears in both tables and thus becomes a join column,
so, according to standard SQL, it should appear only once in the output, not twice. Similarly, in the
second SELECT statement, column
j
is named in the
USING
clause and should appear only once
in the output, not twice. But in both cases, the redundant column is not eliminated. Also, the order of
the columns is not correct according to standard SQL.
Now the statements produce this output:
+------+------+------+
| j | i | k |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
+------+------+------+
| j | i | k |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
The redundant column is eliminated and the column order is correct according to standard SQL:
• First, coalesced common columns of the two joined tables, in the order in which they occur in the
first table
• Second, columns unique to the first table, in order in which they occur in that table
• Third, columns unique to the second table, in order in which they occur in that table
The single result column that replaces two common columns is defined using the coalesce
operation. That is, for two
t1.a
and
t2.a
the resulting single join column
a
is defined as
a =
COALESCE(t1.a, t2.a)
, where:
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)