SQL 知识量:22 - 44 - 129
SQL的联结可以分为:内联结、自联结、自然联结和外联结。
自联结就是在一个select语句中联结多个完全相同的表,也就是表联结自己。
例如:根据id值为1的学生选课情况,查询同样选择了他所选课程的全部学生id和课程id。如果使用子查询,SQL语句如下:
select id_student,id_lesson from course where id_lesson in (select id_lesson from course where id_student=1) order by id_student;
结果:
+------------+-----------+ | id_student | id_lesson | +------------+-----------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 3 | 2 | | 4 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 1 | | 6 | 2 | +------------+-----------+
如果改用自联结,SQL应修改为:
select c1.id_student,c1.id_lesson from course as c1,course as c2 where c2.id_student=1 and c1.id_lesson=c2.id_lesson order by c1.id_student;
查询的结果是一样的。
因为自联结至少有两个表是一模一样的,为避免混淆错误,必须使用表别名加以区分。
通常情况下,在同时可以使用子查询和自联结完成某项查询时,优先选择自联结,因为,许多DBMS处理联结远比处理子查询快得多。
对表进行联结时,至少有一列同时出现在两个表中,这一列也就是被联结的列。在内联结查询中,结果会包含所有列,不同表中相同的列会重复出现。所谓自然联结就是排除这种重复列的联结。但是,DBMS不会自动完成自然联结,要实现自然联结需要你自己完成它,即在查询联结选择检索列时,避免相同的列重复出现。
例如:查询所有学生的信息和选课情况。
select s.*,l.name from student as s inner join course as c inner join lesson as l on s.id=c.id_student and l.id=c.id_lesson order by s.id;
结果:
+----+-------+-----+--------+---------+ | id | name | age | sex | name | +----+-------+-----+--------+---------+ | 1 | Susan | 11 | female | English | | 1 | Susan | 11 | female | Maths | | 2 | Jame | 12 | male | Natural | | 2 | Jame | 12 | male | Maths | | 3 | Bob | 11 | male | English | | 4 | Robot | 10 | male | Maths | | 4 | Robot | 10 | male | English | | 4 | Robot | 10 | male | Natural | | 5 | Jen | 11 | female | English | | 5 | Jen | 11 | female | Natural | | 6 | Toney | 10 | male | Maths | | 6 | Toney | 10 | male | English | +----+-------+-----+--------+---------+
以上查询涉及三个表:学生表(student)、课程表(lesson)、选课表(course),其中,course中的id_student和id_lesson分别与student中的id和lesson中的id关联,也就是重复。通过人为设置检索列,避免重复的id出现在结果中,以实现自然联结。
设置检索列时,通常会用到通配符*,其他要显示的列则根据需要明确列出。
内联结一般都是自然联结。
在联结时,将一个表中的行与另一个表中的行进行关联,但是有时候查询的结果需要包含那些没有关联的行,这种联结就是外联结。
例如:查询所有学生的信息和选课情况。
select s.*,l.name as lesson_name from student as s left outer join course as c on s.id=c.id_student left outer join lesson as l on c.id_lesson=l.id order by s.id;
结果:
+----+--------+-----+--------+-------------+ | id | name | age | sex | lesson_name | +----+--------+-----+--------+-------------+ | 1 | Susan | 11 | female | Maths | | 1 | Susan | 11 | female | English | | 2 | Jame | 12 | male | Maths | | 2 | Jame | 12 | male | Natural | | 3 | Bob | 11 | male | English | | 4 | Robot | 10 | male | Maths | | 4 | Robot | 10 | male | English | | 4 | Robot | 10 | male | Natural | | 5 | Jen | 11 | female | English | | 5 | Jen | 11 | female | Natural | | 6 | Toney | 10 | male | Maths | | 6 | Toney | 10 | male | English | | 7 | Jack | 13 | male | NULL | | 8 | Alice | 10 | female | NULL | | 9 | Bonnie | 11 | male | NULL | | 10 | Ella | 12 | female | NULL | +----+--------+-----+--------+-------------+
以上查询结果中,包含了没有选课的学生信息,即lesson_name列为NULL的学生信息。
在SQL语句中,通过outer join指明是外联结。在使用outer join语法时,必须同时使用right或left关键字。right表明外联结右边的表,即以右边的表为基础进行联结,查询将从右边的表中选择所有行;left则表明外联结左边的表。以上示例中,left outer join表示总是以左边的表为基础表进行联结。
right outer join与left outer join只是联结的方向不同,本质上,它们是一样的。
还有一种不常用的外联结,即全外联结,通过full outer join来设置,与左右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行。
Copyright © 2017-Now pnotes.cn. All Rights Reserved.
编程学习笔记 保留所有权利
MARK:3.0.0.20240214.P35
From 2017.2.6