Rechercher dans le manuel MySQL
13.2.10.2 JOIN Syntax
MySQL supports the following JOIN
syntax for
the table_references
part of
SELECT
statements and
multiple-table DELETE
and
UPDATE
statements:
- table_references:
- escaped_table_reference [, escaped_table_reference] ...
- escaped_table_reference:
- table_reference
- | { OJ table_reference }
- table_reference:
- table_factor
- | join_table
- table_factor:
- | ( table_references )
- join_table:
- | table_reference STRAIGHT_JOIN table_factor
- join_condition:
- ON conditional_expr
- index_hint_list:
- index_hint [, index_hint] ...
- index_hint:
- index_list:
- index_name [, index_name] ...
A table reference is also known as a join expression.
A table reference (when it refers to a partitioned table) may
contain a PARTITION
option, including a list
of comma-separated partitions, subpartitions, or both. This
option follows the name of the table and precedes any alias
declaration. The effect of this option is that rows are selected
only from the listed partitions or subpartitions. Any partitions
or subpartitions not named in the list are ignored. For more
information and examples, see
Section 23.5, “Partition Selection”.
The syntax of table_factor
is
extended in MySQL in comparison with standard SQL. The standard
accepts only table_reference
, not a
list of them inside a pair of parentheses.
This is a conservative extension if each comma in a list of
table_reference
items is considered
as equivalent to an inner join. For example:
is equivalent to:
In MySQL, JOIN
, CROSS
JOIN
, and INNER JOIN
are syntactic
equivalents (they can replace each other). In standard SQL, they
are not equivalent. INNER JOIN
is used with
an ON
clause, CROSS JOIN
is used otherwise.
In general, parentheses can be ignored in join expressions containing only inner join operations. MySQL also supports nested joins. See Section 8.2.1.7, “Nested Join Optimization”.
Index hints can be specified to affect how the MySQL optimizer
makes use of indexes. For more information, see
Section 8.9.4, “Index Hints”. Optimizer hints and the
optimizer_switch
system variable are other
ways to influence optimizer use of indexes. See
Section 8.9.2, “Optimizer Hints”, and
Section 8.9.3, “Switchable Optimizations”.
The following list describes general factors to take into account when writing joins:
A table reference can be aliased using
ortbl_name
ASalias_name
tbl_name alias_name
:A
table_subquery
is also known as a derived table or subquery in theFROM
clause. See Section 13.2.11.8, “Derived Tables”. Such subqueries must include an alias to give the subquery result a table name, and may optionally include a list of table column names in parentheses. A trivial example follows:INNER JOIN
and,
(comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).However, the precedence of the comma operator is less than that of
INNER JOIN
,CROSS JOIN
,LEFT JOIN
, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the formUnknown column '
may occur. Information about dealing with this problem is given later in this section.col_name
' in 'on clause'The
conditional_expr
used withON
is any conditional expression of the form that can be used in aWHERE
clause. Generally, theON
clause serves for conditions that specify how to join tables, and theWHERE
clause restricts which rows to include in the result set.If there is no matching row for the right table in the
ON
orUSING
part in aLEFT JOIN
, a row with all columns set toNULL
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.*
This example finds all rows in
left_tbl
with anid
value that is not present inright_tbl
(that is, all rows inleft_tbl
with no corresponding row inright_tbl
). See Section 8.2.1.8, “Outer Join Optimization”.The
USING(
clause names a list of columns that must exist in both tables. If tablescolumn_list
)a
andb
both contain columnsc1
,c2
, andc3
, the following join compares corresponding columns from the two tables:The
NATURAL [LEFT] JOIN
of two tables is defined to be semantically equivalent to anINNER JOIN
or aLEFT JOIN
with aUSING
clause that names all columns that exist in both tables.RIGHT JOIN
works analogously toLEFT JOIN
. To keep code portable across databases, it is recommended that you useLEFT JOIN
instead ofRIGHT 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.*
You can use other types of joins within
{ OJ ... }
, such asINNER JOIN
orRIGHT OUTER JOIN
. This helps with compatibility with some third-party applications, but is not official ODBC syntax.STRAIGHT_JOIN
is similar toJOIN
, 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 processes the tables in a suboptimal order.
Some join examples:
Natural joins and joins with USING
, including
outer join variants, are processed according to the SQL:2003
standard:
Redundant columns of a
NATURAL
join do not appear. Consider this set of statements:In the first
SELECT
statement, columnj
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, columnj
is named in theUSING
clause and should appear only once in the output, not twice.Thus, the statements produce this output:
+------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+
Redundant column elimination and column ordering occurs according to standard SQL, producing this display order:
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
andt2.a
the resulting single join columna
is defined asa = COALESCE(t1.a, t2.a)
, where:If the join operation is any other join, the result columns of the join consist of the concatenation of all columns of the joined tables.
A consequence of the definition of coalesced columns is that, for outer joins, the coalesced column contains the value of the non-
NULL
column if one of the two columns is alwaysNULL
. If neither or both columns areNULL
, both common columns have the same value, so it doesn't matter which one is chosen as the value of the coalesced column. A simple way to interpret this is to consider that a coalesced column of an outer join is represented by the common column of the inner table of aJOIN
. Suppose that the tablest1(a, b)
andt2(a, c)
have the following contents:t1 t2 ---- ---- 1 x 2 z 2 y 3 w
Then, for this join, column
a
contains the values oft1.a
:- +------+------+------+
- | a | b | c |
- +------+------+------+
- +------+------+------+
By contrast, for this join, column
a
contains the values oft2.a
.- +------+------+------+
- | a | c | b |
- +------+------+------+
- +------+------+------+
Compare those results to the otherwise equivalent queries with
JOIN ... ON
:- +------+------+------+------+
- | a | b | a | c |
- +------+------+------+------+
- +------+------+------+------+
A
USING
clause can be rewritten as anON
clause that compares corresponding columns. However, althoughUSING
andON
are similar, they are not quite the same. Consider the following two queries:With respect to determining which rows satisfy the join condition, both joins are semantically identical.
With respect to determining which columns to display for
SELECT *
expansion, the two joins are not semantically identical. TheUSING
join selects the coalesced value of corresponding columns, whereas theON
join selects all columns from all tables. For theUSING
join,SELECT *
selects these values: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)
is the same as eithera.c1
orb.c1
because both columns will have the same value. With an outer join (such asLEFT JOIN
), one of the two columns can beNULL
. That column is omitted from the result.An
ON
clause can refer only to its operands.Example:
The statement fails with an
Unknown column 'i3' in 'on clause'
error becausei3
is a column int3
, which is not an operand of theON
clause. To enable the join to be processed, rewrite the statement as follows:JOIN
has higher precedence than the comma operator (,
), so the join expressiont1, t2 JOIN t3
is interpreted as(t1, (t2 JOIN t3))
, not as((t1, t2) JOIN t3)
. This affects statements that use anON
clause because that clause can refer only to columns in the operands of the join, and the precedence affects interpretation of what those operands are.Example:
The
JOIN
takes precedence over the comma operator, so the operands for theON
clause aret2
andt3
. Becauset1.i1
is not a column in either of the operands, the result is anUnknown column 't1.i1' in 'on clause'
error.To enable the join to be processed, use either of these strategies:
Group the first two tables explicitly with parentheses so that the operands for the
ON
clause are(t1, t2)
andt3
:Avoid the use of the comma operator and use
JOIN
instead:
The same precedence interpretation also applies to statements that mix the comma operator with
INNER JOIN
,CROSS JOIN
,LEFT JOIN
, andRIGHT JOIN
, all of which have higher precedence than the comma operator.A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of
NATURAL
orUSING
joins, whereas the standard disallows that.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-join.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.