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 语法:

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)