JOIN连接查询
- 左连接
-- A的全集,B没有则值显示为空
SELECT
*
FROM
t_emp a
LEFT JOIN t_dept b ON a.deptId = b.id
- 右连接
-- B的全集,A没有则值为空
SELECT
*
FROM
t_emp a
RIGHT JOIN t_dept b ON a.deptId = b.id
- A表独有
SELECT
*
FROM
t_emp a
LEFT JOIN t_dept b ON a.deptId = b.id
WHERE
b.id IS NULL
- 内连接
-- AB两表共有
SELECT
*
FROM
t_emp a
INNER JOIN t_dept b ON a.deptId = b.id
- B表独有
SELECT
*
FROM
t_emp a
RIGHT JOIN t_dept b ON a.deptId = b.id
where a.deptId is null
- 全表外连接(AB全有)
MYSQL不支持FULL JOIN,下面替代方法:left join + union(可去除重复数据)+ right join
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id
- 全连接(A独有+B独有)
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id WHERE b.id IS NULL
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL
- 自连接(如果连接的表中有列名相同,并且连接的条件就是列明相等,那么on条件可以换成using)
SELECT * FROM t_emp a inner JOIN t_dept b ON a.id = b.id
-- 等效
SELECT * FROM t_emp a inner JOIN t_dept b USING (id)