MySql教程

mysql的几种连接方式

本文主要是介绍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)
这篇关于mysql的几种连接方式的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航