MySQLSELECT语法（三）JOIN语法详解
源⾃MySQL 5.7 官⽅⼿册：
SELECT select_expr
From table_
<
如上所⽰，MySQL⽀持在table_references后添加JOIN选项作为SELECT语句的⼀部分，当然也可以在多表的DELETE和UPDATE。
下⾯列出了JOIN的详细语法：
table_references:
escaped_table_reference [, escaped_table_reference] ...
escaped_table_reference:
table_reference
| { OJ table_reference }
table_reference:
table_factor
| joined_table
table_factor:
tbl_name [PARTITION (partition_names)]
[[AS] alias] [index_hint_list]
| table_subquery [AS] alias
| ( table_references )
joined_table:
table_reference [INNER | CROSS] JOIN table_factor [join_specification]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON search_condition
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
join_specification:
ON search_condition
| USING (join_column_list)
join_column_list:
column_name [, column_name] ...
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| {IGNORE|FORCE} {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
html5网站模板建站index_name [, index_name] ...
⼀、表引⽤（table reference）
⼀个表引⽤也被称为⼀个JOIN表达式。表引⽤（当它引⽤分区表时）可能有PARTITION选项，包括⼀个由逗号分隔的分区，⼦分区或两者皆有的列表。此选项紧跟在的名字之后，并在任何别名声明之前。此选项的作⽤是仅从列出的分区或⼦分区中选择数据⾏，⽽且将忽略列表中未命名的任何分区或⼦分区。see Section 22.5, “Partition Selection”。
table_factor语法是MySQL对标准SQL中的扩展。标准SQL只接受table_reference，⽽不是⼀对括号内的列表。
如果table_reference项列表中的每个逗号被视为内连接（INNER JOIN），则这是保守的扩展。例如：
SELECT*FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)
等价于：
SELECT*FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)
在MySQL中，JOIN，CROSS JOIN，和INNER JOIN 在语义上是等价的，他们可以相互替换。但是在标准SQL中，他们不等
价，INNER JOIN与ON搭配使⽤，CROSS JOIN搭配其它。
⼀般来说，在只有INNER JOIN操作的表达式中，括号可以被省略。MySQL还⽀持嵌套连接，
See Section 8.2.1.7, “Nested Join Optimization”。
指定索引提⽰（Index hints ）能够影响MySQL优化器如何使⽤索引。更多信息，see Section 8.9.4, “Index Hints”.
优化器提⽰和optimizer_switch系统变量是影响优化器使⽤索引的其他⽅法。See Section 8.9.3, “Optimizer Hints”, and Section 8.9.2,“Switchable Optimizations”。
⼆、在编写联接时要考虑的⼀般因素
2.1
可以使⽤tbl_name AS alias_name或tbl_name alias_name对表引⽤定义别名。
会matlab可以工作吗SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
SELECT t1.name, t2.salary
FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
2.2
table_subquery也称为FROM⼦句中的派⽣表或⼦查询。Section 13.2.10.8, “Derived Tables”.
此类⼦查询必须包含别名，以便为⼦查询结果提供表名。⼀个简单的例⼦如下：
SELECT*FROM (SELECT1, 2, 3) AS t1;
/*
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
*/
2.3
在没有连接条件的情况下，INNER JOIN和“，”（逗号）在语义上是等效的——两者都在指定的表之间产⽣笛卡尔积，也就是说，第⼀个表中的每⼀⾏都连接到第⼆个表中的每⼀⾏。
但是，逗号运算符的优先级⽐其它含有“JOIN”的运算符要⼩。如果在存在连接条件时将逗号连接与其他连接类型混合，则可能会报错：Unknown column 'col_name' in 'on clause' 。对这个问题的处理会在⽂章的后⾯讨论。
与ON⼀起使⽤的search_condition是可以在WHERE⼦句中使⽤的任何条件表达式。ON⼦句⽤于指明如多表如何连接，WHERE⼦句则限制要包含在结果集中的⾏。
2.4
在LEFT JOIN中，如果在右表中没有匹配ON或者USING中条件的⾏，则该连接中中的右表的列全都设置为NULL。你可以利⽤这点来查左表A中在右表B中没有任何对应项的⾏：
SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;
这个查询语句会出左表left_tbl中这样的⾏：其ID值在右表right_tbl的ID列中不存在。See Section 8.2.1.8, “Outer Join Optimization”.（外连接包括LEFT JOIN和RIGHT JOIN）
例如，我查学⽣表stu中在成绩表sc中没有任何成绩的学⽣：
select stu.*
from student as stu left join sc on stu.SId=sc.SId
where sc.SId is null;
/*
SId | Sname | Sage | Ssex |
+------+-------+---------------------+------+
| 09 | 张三 | 2017-12-20 00:00:00 | ⼥ |
| 10 | 李四 | 2017-12-25 00:00:00 | ⼥ |
安全员c证考试题库| 11 | 李四 | 2017-12-30 00:00:00 | ⼥ |
| 12 | 赵六 | 2017-01-01 00:00:00 | ⼥ |
| 13 | 孙七 | 2018-01-01 00:00:00 | ⼥ |
+------+-------+---------------------+------+
*/
当然这⾥碰到了⼀个⼩问题，把查询语句的WHERE条件改成sc.SId=null时，取出的是空集：
select stu.*
from student as stu left join sc on stu.SId=sc.SId
where sc.SId=null;
/*
Empty set (0.08 sec)
*/
在WHERE⼦句中，column = null永远不会为true，以这种⽅式使⽤null⽆效，要检测值为NULL的列，必须使⽤IS NULL或列IS NOT NULL。关于NULL的使⽤有专门的章节：Working with NULL Values。
2.5
USING（join_column_list）⼦句指定两个表中必须拥有的列的列表。如果表a和b都包含列c1，c2和c3，则以下连接将⽐较两个表中的相应列：
a LEFT JOIN
b USING (c1, c2, c3)
2.6
两个表的NATURAL [LEFT] JOIN等下于下⾯的情况：带有USING⼦句的INNER JOIN或LEFT JOIN，该⼦句列出了在两个表中都存在的所有的列。
2.7
RIGHT JOIN的⼯作⽅式类似于LEFT JOIN。为了使代码可以跨数据库移植，建议您使⽤LEFT JOIN⽽不是RIGHT JOIN。
2.8
语法描述中的{ OJ...}，只是为了兼容ODBC。这个花括号必须按字⾯编写。
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;
您可以在{OJ ...}中使⽤其他类型的连接，例如INNER JOIN或RIGHT OUTER JOIN。这有助于与某些第三⽅应⽤程序兼容，但不是官⽅ODBC语法。
2.9
STRAIGHT_JOIN类似于JOIN，只是左表始终在右表之前读取。
这可以⽤于连接优化器以次优顺序处理表的那些（少数）情况。
⼀些JOIN⽰例：
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;
Natural join和使⽤USING的JOIN，包括外连接的变体，是根据SQL-2003的标准进⾏处理的。
2.10
NATURAL连接中的冗余列不会显⽰。
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);
第⼀个和第⼆个SELECT语句中的“j”列，都只会出现⼀次：
/*
+------+------+------+
| j | i | k |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
+------+------+------+
| j | i | k |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
*/
冗余列的消除和列的排序都是根据标准SQL进⾏处理，按下⾯的顺序展⽰：
⾸先，合并两个连接表的相同列，按他们在第⼀个表中出现的顺序排列；
然后，第⼀个表所特有的列，按它们在该表中出现的顺序排列；
第三，第⼆个表所特有的列，它们在该表中出现的顺序；
取代两个表的相同列的单列是通过使⽤coalesce（合并）操作来定义的，也就是说，对于两个t1.a和t2.a，得到的单个连接列a被定义为a = COALESCE（t1.a，t2.a）：
COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)
如果任何其他的join操作，则连接的结果列由参与连接的表的所有列的串联组成。合并的列的定义结果是，对于外连接，如果两列中的⼀列始终为NULL，则合并列包含⾮NULL列的值。如果两列都不为NULL或者都为NULL，两个公共列具有相同的值，因此选择哪⼀列作为合并列的值就⽆关紧要了。解释这⼀点的⼀种简单⽅法是考虑外连接的合并列由JOIN的内部表的公共列表⽰。
假设表t1(a,b)和t2(a,c)具有以下内容：
/*
t1 t2
---- ----
1 x
2 z
2 y
3 w
*/
那么下⾯这个JOIN，列a包含的是t1.a的值：
SELECT*FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a | b | c |
+------+------+------+
|1| x |NULL|
|2| y | z |
+------+------+------+
⽽下⾯的JOIN，恰好相反，a列包含的是t2.a的值：
SELECT*FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a | c | b |
java到底是什么编程语言+------+------+------+
|2| z | y |
|3| w |NULL|
+------+------+------+
将这些结果与JOIN ... ON的等效查询进⾏⽐较：
SELECT*FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a | b | a | c |
+------+------+------+------+
|1| x |NULL|NULL|
|2| y |2| z |
+------+------+------+------+
SELECT*FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a | b | a | c |
+------+------+------+------+
|2| y |2| z |
|NULL|NULL|3| w |
+------+------+------+------+
mysql面试题详解 2.11
USING⼦句可以使⽤ON⼦句进⾏重写。尽管他们两个很像，但还是有所不同。
看下下⾯两个查询：
a LEFT JOIN
b USING (c1, c2, c3)
a LEFT JOIN
b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3
在筛选条件上，这两个连接在语义上是⼀致的。但是在“要为SELECT *扩展显⽰哪些列”上，这两个连接在语义上并不相同。USING连接选择相应列的合并值，⽽ON连接选择所有表中的所有列。
对使⽤USING的JOIN，SELECT *选择这些值：
COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)
⽽使⽤ON的JOIN，SELECT *选择如下：
a.c1, a.c2, a.c3,
b.c1, b.c2, b.c3
对于内连接，COALESCE（a.c1，b.c1）与a.c1或b.c1相同，因为两列的值都相同。
对于外连接（例如LEFT JOIN），两列中的⼀列可以为NULL。该列会从结果中略去。
2.12
ON⼦句只能引⽤其操作范围内的操作数。
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
执这个SELECT语句会报错：Unknown column 'i3' in 'on clause' ，因为i3是t3中的⼀列，它不是ON⼦句的操作数。
对此语句进⾏修改：
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
对ON的作⽤范围进⾏测试，以下语句均能执⾏：
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i2 = i3);
Empty set (0.00 sec)
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i2);snipaste
Empty set (0.00 sec)
即ON对其之前的JOIN中的表的列都能引⽤。
2.13
JOIN⽐逗号操作符拥有更⾼的优先级，所以下⾯这个表达式：
t1, t2 JOIN t3
会被解释为：
(t1, (t2 JOIN t3))
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论