MYSQL中JOIN后加条件和WHERE后加条件的区别

 假设有如下三张表

1. r_user 用户表

id	name
1	崔管理员
2	邢客户
3	田客服

1. r_role 角色表

id	name
1	管理员
2	客户
3	客服

1. r_user_role 用户角色表

id	user_id	role_id
1	1	1
2	2	2
3	3	3

一、LEFT JOIN ON后加条件

SELECT
	u.id user_id,
	u.name user_name,
	r.id role_id,
	r.`name` role_name
FROM r_user u 
LEFT JOIN r_user_role ur ON ur.user_id = u.id AND u.id = 1  -- 此处加AND u.id = 1
LEFT JOIN r_role r ON r.id = ur.role_id

结果如下:

可以看到除用户ID为1的用户, 其他用户角色都是NULL。

因为LEFT JOIN会把左表(r_user)所有数据查询出来，ON及其后面的条件仅会影响右表(r_user_role)的数据，符合就显示，不符合全部为NULL, 所以用户ID为1的角色全部为NULL。

LEFT JOIN(主全显,后看ON)主表数据不受影响，即左表的数据会全部显示出来，连接后的表看ON后面的条件，如果没有数据则显示NULL。

二、 RIGHT JOIN ON后加条件

SELECT
	u.id user_id,
	u.name user_name,
	r.id role_id,
	r.`name` role_name
FROM r_user u 
RIGHT JOIN r_user_role ur ON ur.user_id = u.id AND u.id = 1    -- 此处加AND u.id = 1
LEFT JOIN r_role r ON r.id = ur.role_id

结果如下:

可以看到和LEFT JOIN基本一致，只是RIGHT JOIN是以右表为基准，根据ON条件过滤生成临时表。

所以用户ID是NULL，角色信息是有数据的。

LEFT JOIN(主看ON,后全显)右表数据不受影响，即右表的数据会全部显示出来，主表数据看ON后面的条件。

三、INNER JOIN ON后加条件

SELECT
	u.id user_id,
	u.name user_name,
	r.id role_id,
	r.`name` role_name
FROM r_user u 
INNER JOIN r_user_role ur ON ur.user_id = u.id AND u.id = 1  -- 此处加AND u.id = 1
LEFT JOIN r_role r ON r.id = ur.role_id

结果如下:

首先结果是正确的。

因为INNER JOIN根据ON后面的关联条件，两张表中都有的数据才会显示。

可以理解为左右表的交集。

四、 WHERE后加条件

SELECT
	u.id user_id,
	u.name user_name,
	r.id role_id,
	r.`name` role_name
FROM r_user u 
LEFT JOIN r_user_role ur ON ur.user_id = u.id
LEFT JOIN r_role r ON r.id = ur.role_id
WHERE u.id = 1

结果如下:

结果也是正确的

因为WHERE后面加条件与连接本身无关，影响的是连接产生后的数据。

到这一步，又出现一个新问题，既然WHERE和INNER JOIN结果一样，那么应该用哪个呢？

个人建议用INNER JOIN

在INNER JOIN后面加条件是在连接的时候作为条件过滤数据。

在WHERE后面加条件是连接完成后再对结果集进行过滤。

相比较来说INNER JOIN的效率要高，使用INNER JOIN时将最大的表放在第一个，查询语句效率会有明显提高。

在SQL最终执行的时候，数据库通常会把WHERE优化为JOIN来执行。

搬运自:https://www.cnblogs.com/DLdhy/p/17123471.html