连接查询

--（1）相等连接（相等连接使用“=”比较符作为连接条件。）
SELECT teacher_id, name, department_name
  FROM teachers, departments
 WHERE teachers.department_id = Departments.department_id; 
SELECT student_id, name, course_id, score
  FROM students s, students_grade sg
 WHERE s.student_id = sg.student_id;
SELECT course_id, course_name, student_id, score
  FROM courses c, students_grade sg
 WHERE c.course_id = sg.curse_id.

--（2）不等连接
SELECT student_id, score, grade
  FROM students_grade sg, Grades g
 WHERE sg.score BETWEEN g.low_score AND g.high_score;

--（3）复杂内连接
--内连接组合两个或多个表中的数据，结果中含有满足连接条件的所有记录行。
--使用筛选条件：
SELECT teacher_id, name, department_name
  FROM teachers t, departments d
 WHERE t.department_id = d.department_id AND title = '讲师';
SELECT s.student_id, name, course_id, score
  FROM students s, students_grade sg
 WHERE  s.student_id = sg. student_id AND specialty = '计算机';
SELECT c.course_id, course_name, student_id, score
  FROM courses c, students_grade sg
 WHERE c.course_id = sg.course_id AND course_name = 'C++语言程序设计';
SELECT s.student_id, s.name, count(*) AS 所修课程门数
  FROM studnets s, students_grade sg
 WHERE s.student_id = sg.student_id
 GROUP BY s.student_id, s.name
HAVING count(*) > 1
 ORDER BY s.student_id;

--多表连接：
SELECT s.name, course_name, score
  FROM students s, courses c, students_grade sg
 WHERE s.student_id = sg.student_id
   AND c.course_id = sg.course_id;
SELECT s.student_id, s.name, c.course_name, AVG(sg.score) AS '平均成绩'
  FROM students s, course c, students_grade sg
 WHERE student_id = sg.student_id AND c.course_id = sg.course_id
 GROUP BY s.student_id, s.name, c.course_name

（1）左外连接
左外连接查询添加回内连接查询从第一个表中删除的所有行。
NULL值被放入其他表的列中。
非标准SQL外连接查询语句格式：
SELECT teacher_id, name, department_name
  FROM teachers t, departments d
 WHERE t.department_id = d.department_id(+)
SELECT teacher_id, name, department_name
  FROM teachers T
  LEFT OUTER JOIN departments d ON t.department_id = d.department_id; 
（2）右外连接
右外连接查询添加回内连接查询从第二个表中删除的所有行。
SELECT teacher_id, name, department_name
  FROM teachers t, departments d
 WHERE t.department_id(+) = d.department_id;
SELECT teacher_id, name, department_name
  FROM teachers t
 RIGHT OUTER JOIN departments d ON t.department_id = d.department_id;
（3）全外连接
SELECT teacher_id, name, department_name
  FROM teachers t, departments d
 WHERE t.department_id = d.department_id(+)
UNION
SELECT teacher_id, name, department_name
  FROM teachers t, departments d
 WHERE t.department_id(+) = d.department_id;
SELECT teacher_id, name, department_name
  FROM teachers t
  FULL OUTER JOIN departments d ON t.department_id = d.department_id;

（1）交叉连接（笛卡尔乘积）
连接查询没有使用连接条件，这样的连接查询被称为交叉连接（笛卡尔乘积）。
事实上，所有连接操作都源自于交叉连接，它提供了内连接和外连接的基础。
SELECT teacher_id, name, department_name
  FROM teachers, departments
（2）自连接
自连接是某一个表与自身进行的连接查询。表与自身连接似乎只是提供了
相同事物的两个副本，无法提供更多的信息，因此自连接似乎没有任何意义。
然而，自连接确实有意义。所有的数据库每次只能处理表中一行记录。虽然可以
访问一行记录中的所有列，但是只能在一行记录中进行。如果在同一时间需要
同一个表中两个不同行中的信息，则需要将表与自身进行连接。
显示学生与班长的对应信息：
SELECT s1.student_id, s1.name AS 学生名, s1.monitor_id, s2.name AS 班长名
  FROM students s1, students s2
 WHERE s1.monitor_id = s2.student_id(+)
SELECT s1.student_id, s1.name AS 学生名, s1.monitor_id, s2.name AS 班长名
  FROM students s1
  LEFT OUTER JOIN students s2 ON s1.student_id = s2.student_id;