mysql join 相关例子

超、凢脫俗 2023-01-05 12:25 108阅读 0赞

# 表a数据:
    mysql> select * from a;
    +----+-----------+------+
    | id | name      | age  |
    +----+-----------+------+
    | 1  | Pirate    | 10   |
    | 2  | Monkey    | 20   |
    | 3  | Ninja     | 20   |
    | 4  | Spaghetti | 20   |
    +----+-----------+------+
    # 表b数据:
    mysql> select * from b;
    +----+------------+------+
    | id | name       | age  |
    +----+------------+------+
    | 1  | Rutabaga   | 10   |
    | 2  | Pirate     | 20   |
    | 3  | Darth Vade | 20   |1
    | 4  | Ninja      | 20   |
    +----+------------+------+
    
    # inner join: 求交集
    select * from a inner join b on a.name = b.name;
    
    +----+--------+------+-------+---------+--------+
    | id | name   | age  | id(2) | name(2) | age(2) |
    +----+--------+------+-------+---------+--------+
    | 1  | Pirate | 10   | 2     | Pirate  | 20     |
    | 3  | Ninja  | 20   | 4     | Ninja   | 20     |
    +----+--------+------+-------+---------+--------+
    
    # left join,right join 左链左全（左边表记录是全部的）,右链右全（右边表记录是全的）
    select * from a left join b on a.name = b.name;
    +----+-----------+------+-------+---------+--------+
    | id | name      | age  | id(2) | name(2) | age(2) |
    +----+-----------+------+-------+---------+--------+
    | 1  | Pirate    | 10   | 2     | Pirate  | 20     |
    | 3  | Ninja     | 20   | 4     | Ninja   | 20     |
    | 2  | Monkey    | 20   | NULL  | NULL    | NULL   |
    | 4  | Spaghetti | 20   | NULL  | NULL    | NULL   |
    +----+-----------+------+-------+---------+--------+
    
    select * from a right join b on a.name = b.name;
    +------+--------+------+-------+------------+--------+
    | id   | name   | age  | id(2) | name(2)    | age(2) |
    +------+--------+------+-------+------------+--------+
    | 1    | Pirate | 10   | 2     | Pirate     | 20     |
    | 3    | Ninja  | 20   | 4     | Ninja      | 20     |
    | NULL | NULL   | NULL | 1     | Rutabaga   | 10     |
    | NULL | NULL   | NULL | 3     | Darth Vade | 20     |
    +------+--------+------+-------+------------+--------+
    
    # full outer join: 求并集
    # mysql 不支持 full outer join,可以通过两个 join union来实现,没有匹配的记录返回null
    SELECT * FROM a left JOIN b ON a.name = b.name
    union
    SELECT * FROM a right join b ON a.name = b.name;
    
    +------+-----------+------+-------+------------+--------+
    | id   | name      | age  | id(2) | name(2)    | age(2) |
    +------+-----------+------+-------+------------+--------+
    | 1    | Pirate    | 10   | 2     | Pirate     | 20     |
    | 3    | Ninja     | 20   | 4     | Ninja      | 20     |
    | 2    | Monkey    | 20   | NULL  | NULL       | NULL   |
    | 4    | Spaghetti | 20   | NULL  | NULL       | NULL   |
    | NULL | NULL      | NULL | 1     | Rutabaga   | 10     |
    | NULL | NULL      | NULL | 3     | Darth Vade | 20     |
    +------+-----------+------+-------+------------+--------+
    
    
    # 求没有在对方表中出现的记录
    # 求a中有b中没有的记录(b中记录为null): 通过left join来实现(因为a中有,b中没有所以用 a left join b)
    select * from a left join b on a.name = b.name
    where b.name is null;
    +----+-----------+------+-------+---------+--------+
    | id | name      | age  | id(2) | name(2) | age(2) |
    +----+-----------+------+-------+---------+--------+
    | 2  | Monkey    | 20   | NULL  | NULL    | NULL   |
    | 4  | Spaghetti | 20   | NULL  | NULL    | NULL   |
    +----+-----------+------+-------+---------+--------+
    
    # 求b中有a中没有的记录(a 中记录为null): 通过right join来实现(a中没有,b中有,所以用 a right join b)
    select * from a right join b on a.name = b.name
    where a.name is null;
    +------+------+------+-------+------------+--------+
    | id   | name | age  | id(2) | name(2)    | age(2) |
    +------+------+------+-------+------------+--------+
    | NULL | NULL | NULL | 1     | Rutabaga   | 10     |
    | NULL | NULL | NULL | 3     | Darth Vade | 20     |
    +------+------+------+-------+------------+--------+
    
    # full outer join: 求差集:
    # 通过去除并集来实现,效率比较低
    select * from a where a.name not in(
        select a.name from a inner join b on a.name = b.name
        )
    union
    select * from b where b.name not in(
        select a.name from a inner join b on a.name = b.name
    );
    +----+------------+------+
    | id | name       | age  |
    +----+------------+------+
    | 2  | Monkey     | 20   |
    | 4  | Spaghetti  | 20   |
    | 1  | Rutabaga   | 10   |
    | 3  | Darth Vade | 20   |
    +----+------------+------+
    # 通过两个差集合并来实现
    select a.* from a left join b on a.name = b.name where b.name is null
    union all
    select b.* from a right join b b on a.name = b.name where a.name is null;
    
    +----+------------+------+
    | id | name       | age  |
    +----+------------+------+
    | 2  | Monkey     | 20   |
    | 4  | Spaghetti  | 20   |
    | 1  | Rutabaga   | 10   |
    | 3  | Darth Vade | 20   |
    +----+------------+------+
    
    # union 与 union all:
    # union 查询并集并(去掉重复数据): a 表中所有数据,b表中不在a表中出现的数据
    # union all: 查询所有数据,不会去重
    
    # 注意 select * from a union select * from b; 跟 select * from a union all select * from b;结果一样
    # 因为去重是针对所查询出来的==所有列==进行的,对于只查询一行的数据来说,它是针对一行去重的,而对于查询多行数据来说,需要对多行进行去重(多行都完全一样才去重)
    # 而因为多行来说,a表和b表并没有重复的,所以 union 和 union all的效果是一样的,但是性能上来说,union all会更好,因为它省去了去重的步骤
    select a.name from a union select b.name from b;
    +------------+
    | name       |
    +------------+
    | Pirate     |
    | Monkey     |
    | Ninja      |
    | Spaghetti  |
    | Rutabaga   |
    | Darth Vade |
    +------------+
    
    select a.name from a union all select b.name from b;
    +------------+
    | name       |
    +------------+
    | Pirate     |
    | Monkey     |
    | Ninja      |
    | Spaghetti  |
    | Rutabaga   |
    | Pirate     |
    | Darth Vade |
    | Ninja      |
    +------------+
    
    
    # cross join: 笛卡尔积,没有链接条件就会出现笛卡尔积,这种方式很危险,尤其是表比较大的时候
    select * from a cross join b on a.name = b.name;
    +----+-----------+------+-------+------------+--------+
    | id | name      | age  | id(2) | name(2)    | age(2) |
    +----+-----------+------+-------+------------+--------+
    | 1  | Pirate    | 10   | 1     | Rutabaga   | 10     |
    | 2  | Monkey    | 20   | 1     | Rutabaga   | 10     |
    | 3  | Ninja     | 20   | 1     | Rutabaga   | 10     |
    | 4  | Spaghetti | 20   | 1     | Rutabaga   | 10     |
    | 1  | Pirate    | 10   | 2     | Pirate     | 20     |
    | 2  | Monkey    | 20   | 2     | Pirate     | 20     |
    | 3  | Ninja     | 20   | 2     | Pirate     | 20     |
    | 4  | Spaghetti | 20   | 2     | Pirate     | 20     |
    | 1  | Pirate    | 10   | 3     | Darth Vade | 20     |
    | 2  | Monkey    | 20   | 3     | Darth Vade | 20     |
    | 3  | Ninja     | 20   | 3     | Darth Vade | 20     |
    | 4  | Spaghetti | 20   | 3     | Darth Vade | 20     |
    | 1  | Pirate    | 10   | 4     | Ninja      | 20     |
    | 2  | Monkey    | 20   | 4     | Ninja      | 20     |
    | 3  | Ninja     | 20   | 4     | Ninja      | 20     |
    | 4  | Spaghetti | 20   | 4     | Ninja      | 20     |
    +----+-----------+------+-------+------------+--------+
    
    
    # union 和 left join 区别:
    # union 增加行,left join 增加列
    select a.name, b.name from a left join b on a.name = b.name;
    +-----------+---------+
    | name      | name(2) |
    +-----------+---------+
    | Pirate    | Pirate  |
    | Ninja     | Ninja   |
    | Monkey    | NULL    |
    | Spaghetti | NULL    |
    +-----------+---------+
    
    select a.name from a
    union
    select b.name from b;
    
    +------------+
    | name       |
    +------------+
    | Pirate     |
    | Monkey     |
    | Ninja      |
    | Spaghetti  |
    | Rutabaga   |
    | Darth Vade |
    +------------+