SQL-内连接、外连接(左、右)、交叉连接

2022-02-23 15:56:19

本文测试基于以下两个表,student(左) \ teacher(右)，使用数据库MariaDB,图形化界面HeidiSQL.

连接查询的概念：根据两个表或多个表的列之间的关系，从这些表中查询数据,即联合查询通常用连接的方式实现。

一、交叉连接

交叉联接返回左表中每一行与右表中每一行的所有行组合。交叉联接也称作笛卡尔积。有一下两种方法：

select 字段列表 from 表1,表2;

select 字段列表 from 表1 cross join 表2;

实例(两种方法):

MariaDB [test]> select * from student,teacher;

MariaDB [test]> select * from student cross join teacher;

运行结果：

+----+----------+------+----+--------+

| id | name     | cid  | id | name   |

+----+----------+------+----+--------+

|  1 | Geoffrey |    1 |  1 | 张三   |

|  1 | Geoffrey |    1 |  2 | 李四   |

|  1 | Geoffrey |    1 |  3 | 王五   |

|  1 | Geoffrey |    1 |  4 | NULL   |

|  2 | Tracy    |    2 |  1 | 张三   |

|  2 | Tracy    |    2 |  2 | 李四   |

|  2 | Tracy    |    2 |  3 | 王五   |

|  2 | Tracy    |    2 |  4 | NULL   |

|  3 | Benjamin |    1 |  1 | 张三   |

|  3 | Benjamin |    1 |  2 | 李四   |

|  3 | Benjamin |    1 |  3 | 王五   |

|  3 | Benjamin |    1 |  4 | NULL   |

|  4 | William  |    1 |  1 | 张三   |

|  4 | William  |    1 |  2 | 李四   |

|  4 | William  |    1 |  3 | 王五   |

|  4 | William  |    1 |  4 | NULL   |

|  5 | Julie    | NULL |  1 | 张三   |

|  5 | Julie    | NULL |  2 | 李四   |

|  5 | Julie    | NULL |  3 | 王五   |

|  5 | Julie    | NULL |  4 | NULL   |

|  6 | NULL     |    2 |  1 | 张三   |

|  6 | NULL     |    2 |  2 | 李四   |

|  6 | NULL     |    2 |  3 | 王五   |

|  6 | NULL     |    2 |  4 | NULL   |

+----+----------+------+----+--------+

24 rows in set (0.00 sec)

二、外连接

1. 右连接

右表有就显示，左表有则显示，无则填写NULL。

select 字段列表 from 表1 right join 表2 on 条件;

测试中在原命令的基础上，添加了别名和排序。

MariaDB [test]> select a.id, a.name '学生姓名', b.name '老师姓名' from student as a right join teacher as b on a.cid=b.id order by a.id;



+------+--------------+--------------+

| id   | 学生姓名     | 老师姓名     |

+------+--------------+--------------+

| NULL | NULL         | 王五         |

| NULL | NULL         | NULL         |

|    1 | Geoffrey     | 张三         |

|    2 | Tracy        | 李四         |

|    3 | Benjamin     | 张三         |

|    4 | William      | 张三         |

|    6 | NULL         | 李四         |

+------+--------------+--------------+

7 rows in set (0.00 sec)

2. 左连接

左表有就显示，右表有则显示，无则填写NULL。

select 字段列表 from 表1 left join 表2 on 条件;

MariaDB [test]> select a.id, a.name '学生姓名', b.name '老师姓名' from student as a left join teacher as b on a.cid=b.id order by a.id;



+----+--------------+--------------+

| id | 学生姓名     | 老师姓名     |

+----+--------------+--------------+

|  1 | Geoffrey     | 张三         |

|  2 | Tracy        | 李四         |

|  3 | Benjamin     | 张三         |

|  4 | William      | 张三         |

|  5 | Julie        | NULL         |

|  6 | NULL         | 李四         |

+----+--------------+--------------+

6 rows in set (0.00 sec)

三、内连接

显示相互关联的条目。

select 字段列表 from 表1 inner join 表2 on 条件;

MariaDB [test]> select a.id, a.name '学生姓名', b.name '老师姓名' from student as a inner join teacher as b on a.cid=b.id order by a.id;



+----+--------------+--------------+

| id | 学生姓名     | 老师姓名     |

+----+--------------+--------------+

|  1 | Geoffrey     | 张三         |

|  2 | Tracy        | 李四         |

|  3 | Benjamin     | 张三         |

|  4 | William      | 张三         |

|  6 | NULL         | 李四         |

+----+--------------+--------------+

5 rows in set (0.01 sec)

四、总结

注意两个词即可：

基准(如左连接，会以表1为基准，显示条目数量同表1)

关联(通过on条件，两个表达式产生相互作用的两个条目称为有关联，如左连接为以表1为基准，并且可以显示表2中对应信息)

码农公寓