13.2.12 多表连接查询 2. SQL99的连接查询 SQL99
的连接查询与SQL92
的连接查询原理基本相似,不同的是SQL99
连接查询的可读性更强——查询用的多个数据表显式使用xxx join
连接,而不是直接依次排列在from
之后,from
后只需要放一个数据表;连接条件不再放在where
之后,而是提供了专门的连接条件子句 。
交叉连接 交叉连接 (cross join
):交叉连接效果就是SQL92
中的广义笛卡儿积,所以交叉连接无须任何连接条件,如下SQL
语句所示:
1 2 3 4 5 select s.*,teacher_namefrom student_table scross join teacher_table t;
查询结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select s.*,teacher_name # SQL99多表连接查询from后只有一个表名 from student_table s #cross join交叉连接,相当于广义笛卡尔积 cross join teacher_table t; +------------+--------------+--------------+--------------+ | student_id | student_name | java_teacher | teacher_name | +------------+--------------+--------------+--------------+ | 1 | 张三 | 1 | Yeeku | | 1 | 张三 | 1 | Leegang | | 1 | 张三 | 1 | Martine | | 2 | 张三 | 1 | Yeeku | | 2 | 张三 | 1 | Leegang | | 2 | 张三 | 1 | Martine | | 3 | 李四 | 1 | Yeeku | | 3 | 李四 | 1 | Leegang | | 3 | 李四 | 1 | Martine | | 4 | 王五 | 2 | Yeeku | | 4 | 王五 | 2 | Leegang | | 4 | 王五 | 2 | Martine | | 5 | _王五 | 2 | Yeeku | | 5 | _王五 | 2 | Leegang | | 5 | _王五 | 2 | Martine | | 6 | NULL | 2 | Yeeku | | 6 | NULL | 2 | Leegang | | 6 | NULL | 2 | Martine | | 7 | 赵六 | 3 | Yeeku | | 7 | 赵六 | 3 | Leegang | | 7 | 赵六 | 3 | Martine | +------------+--------------+--------------+--------------+
自然连接 自然连接 (natural join
):自然连接表面上看起来也无须指定连接条件,但自然连接是有连接条件的,自然连接会以两个表中的同名列作为连接条件;如果两个表中没有同名列,则自然连接与交叉连接效果完全一样——因为没有连接条件 。如下SQL
语句所示:
1 2 3 4 5 select s.*,teacher_namefrom student_table snatural join teacher_table t;
运行结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select s.*,teacher_name # SQL99多表连接查询from后只有一个表名 from student_table s # natural join 自然连接使用两个表中的同名列作为连接条件 natural join teacher_table t; +------------+--------------+--------------+--------------+ | student_id | student_name | java_teacher | teacher_name | +------------+--------------+--------------+--------------+ | 1 | 张三 | 1 | Yeeku | | 1 | 张三 | 1 | Leegang | | 1 | 张三 | 1 | Martine | | 2 | 张三 | 1 | Yeeku | | 2 | 张三 | 1 | Leegang | | 2 | 张三 | 1 | Martine | | 3 | 李四 | 1 | Yeeku | | 3 | 李四 | 1 | Leegang | | 3 | 李四 | 1 | Martine | | 4 | 王五 | 2 | Yeeku | | 4 | 王五 | 2 | Leegang | | 4 | 王五 | 2 | Martine | | 5 | _王五 | 2 | Yeeku | | 5 | _王五 | 2 | Leegang | | 5 | _王五 | 2 | Martine | | 6 | NULL | 2 | Yeeku | | 6 | NULL | 2 | Leegang | | 6 | NULL | 2 | Martine | | 7 | 赵六 | 3 | Yeeku | | 7 | 赵六 | 3 | Leegang | | 7 | 赵六 | 3 | Martine | +------------+--------------+--------------+--------------+ 21 rows in set
using子句 using
子句连接:using
子句可以指定一列或多列
,用于显式指定两个表中的同名列作为连接条件 。假设两个表中有超过一列的同名列,如果使用natural join
,则会把所有的同名列当成连接条件;使用using
子句,就可显式指定使用哪些同名列
作为连接条件 。如下SQL
语句所示:
1 2 3 4 5 select s.*,teacher_namefrom student_table sjoin teacher_table tusing (teacher_id);
运行结果:
1 2 3 4 5 6 mysql> select s.*,teacher_name from student_table s # join 连接另一个表 join teacher_table t using(teacher_id); 1054 - Unknown column 'teacher_id' in 'from clause'
运行上面语句将出现一个错误,因为student_table
表中并不存在名为teacher_id
的列。也就是说,如果使用using
子句来指定连接条件,则两个表中必须有同名列 ,否则就会出现错误。
on子句连接 on
子句连接:这是最常用的连接方式 ,SQL99
语法的连接条件放在on
子句中指定,而且每个on
子句只指定一个连接条件。这意味着:如果需要进行N表连接,则需要有N-1
个join...on
对。如下SQL
语句所示:
1 2 3 4 select s.*,teacher_namefrom student_table sjoin teacher_table ton s.java_teacher=t.teacher_id;
运行结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select s.*,teacher_name from student_table s join teacher_table t on s.java_teacher=t.teacher_id; +------------+--------------+--------------+--------------+ | student_id | student_name | java_teacher | teacher_name | +------------+--------------+--------------+--------------+ | 1 | 张三 | 1 | Yeeku | | 2 | 张三 | 1 | Yeeku | | 3 | 李四 | 1 | Yeeku | | 4 | 王五 | 2 | Leegang | | 5 | _王五 | 2 | Leegang | | 6 | NULL | 2 | Leegang | | 7 | 赵六 | 3 | Martine | +------------+--------------+--------------+--------------+ 7 rows in set
使用on
子句的连接完全可以代替SQL92
中的等值连接、非等值连接,因为on
子句的连接条件除了等值条件之外,也可以是非等值条件。如下SQL
语句就是SQL99
中的非等值连接。
1 2 3 4 5 select s.*,teacher_namefrom student_table sjoin teacher_table ton s.java_teacher >t.teacher_id;
运行效果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select s.*,teacher_id,teacher_name from student_table s join teacher_table t # 使用on来指定连接条件:非等值连接 on s.java_teacher >t.teacher_id; +------------+--------------+--------------+------------+--------------+ | student_id | student_name | java_teacher | teacher_id | teacher_name | +------------+--------------+--------------+------------+--------------+ | 4 | 王五 | 2 | 1 | Yeeku | | 5 | _王五 | 2 | 1 | Yeeku | | 6 | NULL | 2 | 1 | Yeeku | | 7 | 赵六 | 3 | 1 | Yeeku | | 7 | 赵六 | 3 | 2 | Leegang | +------------+--------------+--------------+------------+--------------+ 5 rows in set
外连接 左、右、全外连接:这三种外连接分别使用left[outer]join
、right[outer]join
和full[outer]join
,这三种外连接的连接条件一样通过on
子句来指定,既可以是等值连接条件,也可以是非等值连接条件。
右外连接 下面使用右外连接,连接条件是非等值连接。
1 2 3 4 select s.*,teacher_id,teacher_namefrom student_table sright join teacher_table ton s.java_teacher<t.teacher_id;
运行效果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select s.*,teacher_id,teacher_name from student_table s right join teacher_table t on s.java_teacher<t.teacher_id; +------------+--------------+--------------+------------+--------------+ | student_id | student_name | java_teacher | teacher_id | teacher_name | +------------+--------------+--------------+------------+--------------+ | NULL | NULL | NULL | 1 | Yeeku | | 1 | 张三 | 1 | 2 | Leegang | | 2 | 张三 | 1 | 2 | Leegang | | 3 | 李四 | 1 | 2 | Leegang | | 1 | 张三 | 1 | 3 | Martine | | 2 | 张三 | 1 | 3 | Martine | | 3 | 李四 | 1 | 3 | Martine | | 4 | 王五 | 2 | 3 | Martine | | 5 | _王五 | 2 | 3 | Martine | | 6 | NULL | 2 | 3 | Martine | +------------+--------------+--------------+------------+--------------+ 10 rows in set
左外连接 下面使用左外连接,连接条件是非等值连接。
1 2 3 4 select s.*,teacher_namefrom student_table sleft join teacher_table ton s.java_teacher>t.teacher_id;
运行结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 mysql> select * from student_table; +------------+--------------+--------------+ | student_id | student_name | java_teacher | +------------+--------------+--------------+ | 1 | 张三 | 1 | | 2 | 张三 | 1 | | 3 | 李四 | 1 | | 4 | 王五 | 2 | | 5 | _王五 | 2 | | 6 | NULL | 2 | | 7 | 赵六 | 3 | +------------+--------------+--------------+ 7 rows in set mysql> select * from teacher_table; +------------+--------------+ | teacher_id | teacher_name | +------------+--------------+ | 1 | Yeeku | | 2 | Leegang | | 3 | Martine | +------------+--------------+ 3 rows in set mysql> select s.*,teacher_id,teacher_name from student_table s left join teacher_table t on s.java_teacher>t.teacher_id; +------------+--------------+--------------+------------+--------------+ | student_id | student_name | java_teacher | teacher_id | teacher_name | +------------+--------------+--------------+------------+--------------+ | 1 | 张三 | 1 | NULL | NULL | | 2 | 张三 | 1 | NULL | NULL | | 3 | 李四 | 1 | NULL | NULL | | 4 | 王五 | 2 | 1 | Yeeku | | 5 | _王五 | 2 | 1 | Yeeku | | 6 | NULL | 2 | 1 | Yeeku | | 7 | 赵六 | 3 | 1 | Yeeku | | 7 | 赵六 | 3 | 2 | Leegang | +------------+--------------+--------------+------------+--------------+ 8 rows in set
运行上面两条外连接语句并查看它们的运行结果,不难发现SQL99
外连接与SQL92
外连接恰好相反,SQL99
左外连接将会把左边表中所有不满足连接条件的记录
全部列出;SQL99
右外连接将会把右边表中所有不满足连接条件的记录全部列出 。
全外连接 下面的SQL
语句使用全外连接,连接条件是等值连接。
1 2 3 4 select s.*,teacher_namefrom student_table sfull join teacher_table ton s.java_teacher=t.teacher_id;
运行错误:
1 2 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full join teacher_table ton s.java_teacher=t.teacher_id' at line 3
SQL99
的全外连接将会把两个表中所有不满足连接条件的记录全部列出 。 注意运行上面查询语句时会出现错误,这是因为MySQL
并不支持全外连接 。
原文链接: 13.2.12 多表连接查询 2. SQL99的连接查询