![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961114.webp)
SELECT
Syntax
1094
• With respect to determining which columns to display for
SELECT *
expansion, the two joins are
not semantically identical. The
USING
join selects the coalesced value of corresponding columns,
whereas the
ON
join selects all columns from all tables. For the preceding
USING
join,
SELECT *
selects these values:
COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
For the
ON
join,
SELECT *
selects these values:
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
With an inner join,
COALESCE(a.c1,b.c1)
[878]
is the same as either
a.c1
or
b.c1
because
both columns will have the same value. With an outer join (such as
LEFT JOIN
), one of the two
columns can be
NULL
. That column will be omitted from the result.
• The evaluation of multi-way natural joins differs in a very important way that affects the result of
NATURAL
or
USING
joins and that can require query rewriting. Suppose that you have three tables
t1(a,b)
,
t2(c,b)
, and
t3(a,c)
that each have one row:
t1(1,2)
,
t2(10,2)
, and
t3(7,10)
.
Suppose also that you have this
NATURAL JOIN
on the three tables:
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
Previously, the left operand of the second join was considered to be
t2
, whereas it should be the
nested join
(t1 NATURAL JOIN t2)
. As a result, the columns of
t3
are checked for common
columns only in
t2
, and, if
t3
has common columns with
t1
, these columns are not used as equi-
join columns. Thus, previously, the preceding query was transformed to the following equi-join:
SELECT ... FROM t1, t2, t3
WHERE t1.b = t2.b AND t2.c = t3.c;
That join is missing one more equi-join predicate
(t1.a = t3.a)
. As a result, it produces one row,
not the empty result that it should. The correct equivalent query is this:
SELECT ... FROM t1, t2, t3
WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
If you require the same query result in current versions of MySQL as in older versions, rewrite the
natural join as the first equi-join.
• Previously, the comma operator (
,
) and
JOIN
both had the same precedence, so the join expression
t1, t2 JOIN t3
was interpreted as
((t1, t2) JOIN t3)
. Now
JOIN
has higher precedence,
so the expression is interpreted as
(t1, (t2 JOIN t3))
. This change affects statements that
use an
ON
clause, because that clause can refer only to columns in the operands of the join, and the
change in precedence changes interpretation of what those operands are.
Example:
CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
Previously, the
SELECT
was legal due to the implicit grouping of
t1,t2
as
(t1,t2)
. Now the
JOIN
takes precedence, so the operands for the
ON
clause are
t2
and
t3
. Because
t1.i1
is not a
column in either of the operands, the result is an
Unknown column 't1.i1' in 'on clause'
error. To allow the join to be processed, group the first two tables explicitly with parentheses so that
the operands for the
ON
clause are
(t1,t2)
and
t3
:
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...