mysql的几种连接方式

测试数据

## 连接表测试数据

select * from aa;
+----+--------+
| id | userid |
+----+--------+
|  1 |     10 |
|  2 |     11 |
|  3 |     12 |
|  4 |     13 |
|  7 |     16 |
|  8 |     17 |
|  9 |     18 |
+----+--------+
7 rows in set (0.00 sec)

select * from bb;
+----+--------+
| id | userid |
+----+--------+
|  1 |     16 |
|  2 |     17 |
|  3 |     18 |
|  4 |     20 |
+----+--------+
4 rows in set (0.00 sec)

1.笛卡尔积

• 结果行数：左表行数*右表行数

+----+--------+----+--------+
| id | userid | id | userid |
+----+--------+----+--------+
|  1 |     10 |  4 |     20 |
|  1 |     10 |  3 |     18 |
|  1 |     10 |  2 |     17 |
|  1 |     10 |  1 |     16 |
|  2 |     11 |  4 |     20 |
|  2 |     11 |  3 |     18 |
|  2 |     11 |  2 |     17 |
|  2 |     11 |  1 |     16 |
|  3 |     12 |  4 |     20 |
|  3 |     12 |  3 |     18 |
|  3 |     12 |  2 |     17 |
|  3 |     12 |  1 |     16 |
|  4 |     13 |  4 |     20 |
|  4 |     13 |  3 |     18 |
|  4 |     13 |  2 |     17 |
|  4 |     13 |  1 |     16 |
|  7 |     16 |  4 |     20 |
|  7 |     16 |  3 |     18 |
|  7 |     16 |  2 |     17 |
|  7 |     16 |  1 |     16 |
|  8 |     17 |  4 |     20 |
|  8 |     17 |  3 |     18 |
|  8 |     17 |  2 |     17 |
|  8 |     17 |  1 |     16 |
|  9 |     18 |  4 |     20 |
|  9 |     18 |  3 |     18 |
|  9 |     18 |  2 |     17 |
|  9 |     18 |  1 |     16 |
+----+--------+----+--------+
28 rows in set (0.00 sec)

2.左连接（left join）

• 结果集是左表全部保留，右表关联不上用null表示
• 结果行数：左表行数

select * from aa left join bb on aa.userid=bb.userid;
+----+--------+------+--------+
| id | userid | id   | userid |
+----+--------+------+--------+
|  7 |     16 |    1 |     16 |
|  8 |     17 |    2 |     17 |
|  9 |     18 |    3 |     18 |
|  1 |     10 | NULL |   NULL |
|  2 |     11 | NULL |   NULL |
|  3 |     12 | NULL |   NULL |
|  4 |     13 | NULL |   NULL |
+----+--------+------+--------+
7 rows in set (0.00 sec)

3.右连接（right join）

• 结果集是右表全部保留，左表关联不上用null表示
• 结果行数：右表行数

select * from aa right join bb on aa.userid=bb.userid;
+------+--------+----+--------+
| id   | userid | id | userid |
+------+--------+----+--------+
|    7 |     16 |  1 |     16 |
|    8 |     17 |  2 |     17 |
|    9 |     18 |  3 |     18 |
| NULL |   NULL |  4 |     20 |
+------+--------+----+--------+
4 rows in set (0.00 sec)

4.内连接（inner join）

• 等值连接，结果集保留量表中的交接的记录

select * from aa inner join bb on aa.userid=bb.userid;
+----+--------+----+--------+
| id | userid | id | userid |
+----+--------+----+--------+
|  7 |     16 |  1 |     16 |
|  8 |     17 |  2 |     17 |
|  9 |     18 |  3 |     18 |
+----+--------+----+--------+
3 rows in set (0.00 sec)

5.左表独有

• 查询左表独有的数据

select * from aa left join bb on aa.userid=bb.userid where bb.userid is null;
+----+--------+------+--------+
| id | userid | id   | userid |
+----+--------+------+--------+
|  1 |     10 | NULL |   NULL |
|  2 |     11 | NULL |   NULL |
|  3 |     12 | NULL |   NULL |
|  4 |     13 | NULL |   NULL |
+----+--------+------+--------+
4 rows in set (0.00 sec)

6.右表独有

• 查询右表独有的数据

select * from aa right join bb on aa.userid=bb.userid where aa.userid is null;
+------+--------+----+--------+
| id   | userid | id | userid |
+------+--------+----+--------+
| NULL |   NULL |  4 |     20 |
+------+--------+----+--------+
1 row in set (0.00 sec)

7.全连接

• 关联表的所有记录
• 通过的union实现
• 结果行数：左表行数+右表行数

select * from aa left join bb on aa.userid=bb.userid
union
select * from aa right join bb on aa.userid=bb.userid;
+------+--------+------+--------+
| id   | userid | id   | userid |
+------+--------+------+--------+
|    7 |     16 |    1 |     16 |
|    8 |     17 |    2 |     17 |
|    9 |     18 |    3 |     18 |
|    1 |     10 | NULL |   NULL |
|    2 |     11 | NULL |   NULL |
|    3 |     12 | NULL |   NULL |
|    4 |     13 | NULL |   NULL |
| NULL |   NULL |    4 |     20 |
+------+--------+------+--------+
8 rows in set (0.00 sec)

8.并集去交集

select * from aa left join bb on aa.userid=bb.userid where bb.userid is null
union
select * from aa right join bb on aa.userid=bb.userid where aa.userid is null;
+------+--------+------+--------+
| id   | userid | id   | userid |
+------+--------+------+--------+
|    1 |     10 | NULL |   NULL |
|    2 |     11 | NULL |   NULL |
|    3 |     12 | NULL |   NULL |
|    4 |     13 | NULL |   NULL |
| NULL |   NULL |    4 |     20 |
+------+--------+------+--------+
5 rows in set (0.00 sec)