1. 结论
- 对左表的限制条件:一定要写在 where 语句中
- 对右表的限制,写在 on 语句中:显示
所有的
结果数,右表中不符合的用 null 表示 - 对右表的限制,写在 where 语句中:显示
部分
结果数,右表中不符合的不显示
2. 准备数据
为了说明效果,写了个简单的例子,可能数据不是很恰当
tb_user表:
tb_name表:
3. 编写SQL语句
对右表的限制条件写在on 语句中:
SELECT
u.id AS "user_id",
u.name_id AS "tb_name_id",
u.sex AS "sex",
n.name AS "name"
FROM
tb_user u
-- =========== 写在 on 条件里 =========
LEFT JOIN tb_name n ON u.name_id = n.id AND n.isDelete = 'N'
WHERE
u.sex = "男"
对右表的限制条件写在where 语句中:
SELECT
u.id AS "user_id",
u.name_id AS "tb_name_id",
u.sex AS "sex",
n.NAME AS "name"
FROM
tb_user u
LEFT JOIN tb_name n ON u.name_id = n.id
WHERE
u.sex = "男"
-- =========== 写在 where 条件里 =========
AND n.isDelete = 'N'