左连接有2个位置可以添加条件,一个是ON + conditional_expr,一个是WHERE + conditional_expr.
两种方式表达的意思不一样,返回的数据也就不一样.
例如:
mysql> select * from test1 left join test2 on test1.a=test2.a and test1.b=1;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 2 | 2 | NULL | NULL |
| 3 | 3 | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)
mysql> select * from test1 left join test2 on test1.a=test2.a where test1.b=1;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
+------+------+------+------+
2 rows in set (0.00 sec)Join 语法:
table_references:
escaped_table_reference [, escaped_table_reference] ...
escaped_table_reference:
table_reference
| { OJ table_reference }
table_reference:
table_factor
| join_table
table_factor:
tbl_name [PARTITION (partition_names)]
[[AS] alias] [index_hint_list]
| table_subquery [AS] alias
| ( table_references )
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] ...
其中关于conditional_expr
官方的解释:
The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.
大意: ON 后面的conditional_expr 可以使用WHERE一样语法,但是ON 后面是2个表连接的条件,WHERE是结果集过滤的条件.
举个例子:
建立2个测试表:
mysql> create table test1( a int ,b int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table test2( a int ,b int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values (1,1) ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (2,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (3,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 values (1,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test1;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from test2;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | 2 |
+------+------+
2 rows in set (0.00 sec)左连接会返回主表test1的所有行,然后根据ON 后面的条件查找test2的记录,如果没有匹配记录则为NULL:
返回主表记录,并且根据test1.a=test2.a查找test2的记录,没有则返回NULL.
mysql> select * from test1 left join test2 on test1.a=test2.a;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 2 | 2 | NULL | NULL |
| 3 | 3 | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)返回主表记录,并且根基test1.a=test2.a and test1.b=1 查找test2的记录,没有则返回NULL.
mysql> select * from test1 left join test2 on test1.a=test2.a and test1.b=1;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 2 | 2 | NULL | NULL |
| 3 | 3 | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)返回主表记录,并且根基test1.a=test2.a and test2.b=1 查找test2的记录,没有则返回NULL.
mysql> select * from test1 left join test2 on test1.a=test2.a and test2.b=1;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | NULL | NULL |
| 3 | 3 | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)如果想返回主表a<=2的记录,这样是不行的,ON 后面的条件test1.a<=2 只是关联test2获取记录的条件.
mysql> select * from test1 left join test2 on test1.a=test2.a and test1.a<=2;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 2 | 2 | NULL | NULL |
| 3 | 3 | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)需要放到WHERE 后面,过滤结果集.
mysql> select * from test1 left join test2 on test1.a=test2.a where test1.a<=2;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 2 |
| 2 | 2 | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)