70
JOIN ON 和 WHERE 條件
左連接有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 語法:
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.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)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)最後更新：2017-10-30 16:04:06