MySQL的7种连接彻底清楚了！

青旅半醒 2023-02-14 02:28 17阅读 0赞

不说话先看图吧：  
![在这里插入图片描述][20180528012600526]

SQL的7种连接分别是：inner join、left join、right join、full outer join（2种），但MySQL的full outer join方式却有些不一样。

1.  内连接（交集）
    
        select * from x_user a inner join x_school b on a.id=b.id;
2.  左外连接
    
        select * from x_user a left join x_school b on a.id=b.id;
3.  右外连接
    
        select * from x_user a right join x_school b on a.id=b.id;
4.  左差集
    
        select * from x_user a left join x_school b on a.id=b.id where b.id is null;
5.  右差集
    
        select * from x_user a right join x_school b on a.id=b.id where b.id is null;
6.  并集
    
        select * from x_user a left join x_school b on a.id=b.id union select * from x_user a right join x_school b on a.id=b.id;
    
    并集就是交叉和不交叉的都显示出来，但需要注意的是union左右两边查询的字段个数要保持一致；另外如果同一列下，union左侧显示左边的字段，右边显示的写右边的字段，那么需要在左右分别显示的写上各自的字段就好了，比如下面的1.drugstore\_name, t1.organ\_sign, t1.province, t1.city和 t2.drugstore\_name, t2.organ\_sign, t2.province, t2.city是一一对应的，那么查询出来的就是各自的字段，如下表格图（红色画框的是表t2字段的数据）。
    
        SELECT
        t1.drugstore_name,
        t1.organ_sign,
        t1.province,
        t1.city,
        t1.order_id sku_10_order_id,
        t1.member_guid sku_10_member_guid,
        t1.sku_10_amount,
        t2.order_id sku_1_order_id,
        t2.member_guid sku_1_member_guid,
        t2.sku_1_amount 
        FROM	t1	LEFT JOIN t2 ON t1.organ_sign = t2.organ_sign 	AND t1.order_id = t2.order_id 
        UNION
        SELECT
        t2.drugstore_name,
        t2.organ_sign,
        t2.province,
        t2.city,
        t1.order_id,
        t1.member_guid,
        t1.sku_10_amount,
        t2.order_id sku_1_order_id,
        t2.member_guid sku_1_member_guid,
        t2.sku_1_amount 
        FROM
        t1	RIGHT JOIN t2 ON t1.organ_sign = t2.organ_sign 	AND t1.order_id = t2.order_id;
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1MDAzNTA1_size_16_color_FFFFFF_t_70]
7.  差集
    
        select * from x_user a left join x_school b on a.id=b.id where b.id is  null union select * from x_user a right join x_school b on a.id=b.id where a.id is null;

[20180528012600526]: https://img-blog.csdn.net/20180528012600526
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1MDAzNTA1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200531114753972.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE1MDAzNTA1,size_16,color_FFFFFF,t_70