一个简单例子例子说明Mysql的七种join连接

迷南。 2022-12-24 15:48 154阅读 0赞

先放一张文氏图震贴  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ1OTg2Ng_size_4_color_FFFFFF_t_70][]

--------------------

现有数据如下

mysql> select * from user;
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | lisi     |
    |  4 | zhaoliu  |
    +----+----------+
    
    mysql> select * from user_info;
    +----+------+
    | id | age  |
    +----+------+
    |  1 |   20 |
    |  2 |   21 |
    |  3 |   23 |
    +----+------+

1.  首先谈一种笛卡尔积，即没有连接条件

mysql> select * from user, user_info;
    +----+----------+----+------+
    | id | name     | id | age  |
    +----+----------+----+------+
    |  1 | zhangsan |  1 |   20 |
    |  2 | lisi     |  1 |   20 |
    |  4 | zhaoliu  |  1 |   20 |
    |  1 | zhangsan |  2 |   21 |
    |  2 | lisi     |  2 |   21 |
    |  4 | zhaoliu  |  2 |   21 |
    |  1 | zhangsan |  3 |   23 |
    |  2 | lisi     |  3 |   23 |
    |  4 | zhaoliu  |  3 |   23 |
    +----+----------+----+------+
    # 可以看出两张表每一行都各自与另一张表的每一行组合

1.  内连接，即取交集，可以写inner join 或 直接写join。即默认的Join就是inner join

mysql> select * from user join user_info on user.id = user_info.id;
    +----+----------+----+------+
    | id | name     | id | age  |
    +----+----------+----+------+
    |  1 | zhangsan |  1 |   20 |
    |  2 | lisi     |  2 |   21 |
    +----+----------+----+------+

1.  左外连接，这应该是关系型数据库用的最多的情况，即外键一对多，对左表数据进行扩充

mysql> select * from user left join user_info on user.id = user_info.id;
    +----+----------+------+------+
    | id | name     | id   | age  |
    +----+----------+------+------+
    |  1 | zhangsan |    1 |   20 |
    |  2 | lisi     |    2 |   21 |
    |  4 | zhaoliu  | NULL | NULL |
    +----+----------+------+------+
    # 可以看出，当赵六没有年龄，就被置空了。

1.  左内连接，去掉交集的部分，**在左外连接的情况下，不要右表的任何数据**

select * from user left join user_info on user.id = user_info.id where user_info.id is null;
    +----+---------+------+------+
    | id | name    | id   | age  |
    +----+---------+------+------+
    |  4 | zhaoliu | NULL | NULL |
    +----+---------+------+------+
    # 可以看出，我要的就是右表为空的数据

1.  右外连接，以右表为标准，即扩充右表

mysql> select * from user right join user_info on user.id = user_info.id;
    +------+----------+----+------+
    | id   | name     | id | age  |
    +------+----------+----+------+
    |    1 | zhangsan |  1 |   20 |
    |    2 | lisi     |  2 |   21 |
    | NULL | NULL     |  3 |   23 |
    +------+----------+----+------+
    # 结合左外就很容易理解

1.  右内连接，同理就是不要左表的数据

mysql> select * from user right join user_info on user.id = user_info.id where user.id is null;
    +------+------+----+------+
    | id   | name | id | age  |
    +------+------+----+------+
    | NULL | NULL |  3 |   23 |
    +------+------+----+------+

1.  全外连接，full outer join，即选择所有数据，但要注意交集的部分去重

select * from user left join user_info on user.id = user_info.id
        -> union
        -> select * from user right join user_info on user.id = user_info.id;
    +------+----------+------+------+
    | id   | name     | id   | age  |
    +------+----------+------+------+
    |    1 | zhangsan |    1 |   20 |
    |    2 | lisi     |    2 |   21 |
    |    4 | zhaoliu  | NULL | NULL |
    | NULL | NULL     |    3 |   23 |
    +------+----------+------+------+
    # 可以看出的是使用了union进行了去重

1.  全内连接(名字不准确…)， 即只要差集的部分

select * from user left join user_info on user.id = user_info.id where user_info.id is null
        -> union
        -> select * from user right join user_info on user.id = user_info.id where user.id is null;
    +------+---------+------+------+
    | id   | name    | id   | age  |
    +------+---------+------+------+
    |    4 | zhaoliu | NULL | NULL |
    | NULL | NULL    |    3 |   23 |
    +------+---------+------+------+

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ1OTg2Ng_size_4_color_FFFFFF_t_70]: /images/20221120/08111eb94040436bac52b5a2d9c10156.png