测试

表1：table2

id No

1 n1

2 n2

3 n3

表2：table2

No name

n1 aaa

n2 bbb

n3 ccc

首先创建下面的两个表

CREATE TABLE `t2` (  `no` varchar(11) NOT NULL,  `name` varchar(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t1` (  `id` tinyint(4) NOT NULL,  `no` varchar(11) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from t1;+----+----+| id | no |+----+----+|  1 | n1 ||  2 | n2 ||  3 | n3 ||  4 | n4 |+----+----+4 rows in setmysql> select * from t2;+----+------+| no | name |+----+------+| n1 | aaa  || n2 | bbb  || n3 | ccc  |+----+------+3 rows in setmysql>

现在我们来对上面的数据进行操作；

1、测试场景一 t1表左连接t2表 右连接 inner 连接t2表

mysql> select * from t1 join t2 on t1.no = t2.no;+----+----+----+------+| id | no | no | name |+----+----+----+------+|  1 | n1 | n1 | aaa  ||  2 | n2 | n2 | bbb  ||  3 | n3 | n3 | ccc  |+----+----+----+------+3 rows in setmysql> select * from t1 left join t2 on t1.no = t2.no;+----+----+------+------+| id | no | no   | name |+----+----+------+------+|  1 | n1 | n1   | aaa  ||  2 | n2 | n2   | bbb  ||  3 | n3 | n3   | ccc  ||  4 | n4 | NULL | NULL |+----+----+------+------+4 rows in setmysql> select * from t1 right join t2 on t1.no = t2.no;+----+----+----+------+| id | no | no | name |+----+----+----+------+|  1 | n1 | n1 | aaa  ||  2 | n2 | n2 | bbb  ||  3 | n3 | n3 | ccc  |+----+----+----+------+3 rows in setmysql>

 

测试场景2：在left join 中 使用on 和where 给t2表添加限制条件，二者的区别为

mysql> select * from t1 left join t2 on t1.no = t2.no and t2.name = 'aaa';+----+----+------+------+| id | no | no   | name |+----+----+------+------+|  1 | n1 | n1   | aaa  ||  2 | n2 | NULL | NULL ||  3 | n3 | NULL | NULL ||  4 | n4 | NULL | NULL |+----+----+------+------+4 rows in set

 

mysql> select * from t1 left join t2 on t1.no = t2.no where t2.name = 'aaa';+----+----+----+------+| id | no | no | name |+----+----+----+------+|  1 | n1 | n1 | aaa  |+----+----+----+------+1 row in set

 

从测试2我们可以看出：

结论：

首先明确两个概念：

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在left join下，两者的区别：

on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。

where则是在生成临时表之后使用的条件，此时已经不管是否使用了left join了，只要条件不为真的行，全部过滤掉。

 

 

如果使用inner join 二者查询的结果是一致的，这里要注意

mysql> select * from t1  join t2 on t1.no = t2.no where t2.name = 'aaa';+----+----+----+------+| id | no | no | name |+----+----+----+------+|  1 | n1 | n1 | aaa  |+----+----+----+------+1 row in setmysql> select * from t1  join t2 on t1.no = t2.no and t2.name = 'aaa';+----+----+----+------+| id | no | no | name |+----+----+----+------+|  1 | n1 | n1 | aaa  |+----+----+----+------+1 row in set

 

user表:

id | name

---------

1 | libk

2 | zyfon

3 | daodao

user_action表:

user_id | action

---------------

1 | jump

1 | kick

1 | jump

2 | run

4 | swim

sql:

select id, name, action from user as u

left join user_action a on u.id = a.user_id

result:

id | name | action

--------------------------------

1 | libk | jump ①

1 | libk | kick ②

1 | libk | jump ③

2 | zyfon | run ④

3 | daodao | null ⑤

分析：

注意到user_action中还有一个user_id=4, action=swim的纪录，但是没有在结果中出现，

而user表中的id=3, name=daodao的用户在user_action中没有相应的纪录，但是却出现在了结果集中

因为现在是left join，所有的工作以left为准.

结果1，2，3，4都是既在左表又在右表的纪录，5是只在左表，不在右表的纪录

结论：

我们可以想象left join 是这样工作的

从左表读出一条，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行，如：结果1和结果3)，

如果右边没有与on条件匹配的表，那连接的字段都是null.

然后继续读下一条。

引申：

我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表，不在右表的纪录， 注意用来判断的那列必须声明为not null的。

如：

sql:

select id, name, action from user as u

left join user_action a on u.id = a.user_id

where a.user_id is NULL

(注意:1.列值为null应该用is null 而不能用=NULL

2.这里a.user_id 列必须声明为 NOT NULL 的)

result:

id | name | action

--------------------------

3 | daodao | NULL

 

总结：on 是表的连接条件，where是在表形成之后对表的过滤条件