1 多表连接语法

SELECT	table1.column, table2.column
FROM	table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 
  ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2 
  ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];

2 natural join与using

2.1 natural join

可以自动的根据两张表中名字和数据类型匹配的列来连接。而且只针对这种情况的连接，如果有相同的列名但时数据类型不匹配，那么就会报错。
如：

SELECT  department_id, department_name, location_id, city
  FROM    departments
  NATURAL JOIN locations
  WHERE   department_id IN (20, 50);

统计20号和50号部门的信息。其中departments和locations表中只有department_id列名且数据类型一致。

2.2 using

如果多个列有相同的列名但是部分列数据类型不匹配，使用using子句来指定等值连接的列。natual join 和 using子句是互相排斥的。
如：

SELECT employee_id, last_name, location_id, department_id
FROM   employees JOIN departments
USING (department_id) ;

统计各个部门的员工信息。
当与USING子句连接时，不能限定在USING子句本身中使用的列。如果在SQL语句中的任何地方使用了该列，则不能给它加别名指定。
如：

SELECT l.city, d.department_name 
FROM   locations l JOIN departments d
USING (location_id)
WHERE d.location_id = 1400;
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier

以上语句不应该为WHERE子句中的location_id列别名，因为该列在USING子句中使用。
更正：

SELECT l.city, d.department_name 
FROM   locations l JOIN departments d
USING (location_id)
WHERE location_id = 1400;

多表连接语句中，表中相同的列在select中做限定输出 ，需要加表别名区分。否则提示==“column ambiguously defined==”
如下语句中manager_id在两个表中都有。

SELECT first_name, d.department_name, manager_id
FROM   employees e JOIN departments d USING (department_id)
WHERE  department_id = 50;
ERROR at line 1:
ORA-00918: column ambiguously defined

更正：

SELECT first_name, d.department_name, d.manager_id
FROM   employees e JOIN departments d USING (department_id)
WHERE  department_id = 50;

3 练习题

desc student
student_id number
student_name varchar2(10)
faculty_id varchar2(10)
location_id number
desc faculty
faculty_id number
faculty_name varchar2(10)
location_id number

如下两个语句中哪个语句可以执行成功：
SQL> SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty
USING (faculty_id, location_id) GROUP BY faculty_name; Statement 2
SQL> SELECT faculty_name,COUNT(student_id) FROM student NATURAL JOIN faculty
GROUP BY faculty_name;

【答题小贴士】：
1、以墨天轮文章的形式解析题目并给出答案
2、将墨天轮文章链接发送到此文的评论区