join连接查询八种

水深无声 2024-03-26 12:15 49阅读 0赞

##### JOIN连接查询 #####

1.  左连接

-- A的全集，B没有则值显示为空
    SELECT
    	* 
    FROM
    	t_emp a
    	LEFT JOIN t_dept b ON a.deptId = b.id

![image.png][]

1.  右连接

-- B的全集，A没有则值为空
    SELECT
    	* 
    FROM
    	t_emp a
    	RIGHT JOIN t_dept b ON a.deptId = b.id

![image.png][image.png 1]

1.  A表独有

SELECT
    	* 
    FROM
    	t_emp a
    	LEFT JOIN t_dept b ON a.deptId = b.id 
    WHERE
    	b.id IS NULL

![image.png][image.png 2]

1.  内连接

-- AB两表共有
    SELECT
    	* 
    FROM
    	t_emp a
    	INNER JOIN t_dept b ON a.deptId = b.id

![image.png][image.png 3]

1.  B表独有

SELECT
    	* 
    FROM
    	t_emp a
    	RIGHT  JOIN t_dept b ON a.deptId = b.id 
    	where a.deptId is null

![image.png][image.png 4]

1.  全表外连接（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

![image.png][image.png 5]

1.  全连接（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

![image.png][image.png 6]

1.  自连接（如果连接的表中有列名相同，并且连接的条件就是列明相等，那么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)

![image.png][image.png 7]

[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/25905fb37286446db72165d54415a47d.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/2e8c9d650c3d4ff1a85b75567be2f748.png
[image.png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/332367c3aad247628bff026fe46c8d3b.png
[image.png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f60a7086d54748e79bee65ddb2e93c79.png
[image.png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/fbe5fd41509345d6be9e0708238bf353.png
[image.png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d84b1cdc898d4c2886b9adc2d7b82073.png
[image.png 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/e5c14b2c787a451681e76fb2bafbbf9f.png
[image.png 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/6dd954d4f98445509e6b41f3fd7f3513.png