（本博文采用的数据库是Oracle自带的hr用户范本数据库)
多表查询的准备工作
在hr用户下，创建A表,并插入数值。
--创建表A--
CREATE TABLE A (ID NUMBER);
INSERT INTO A VALUES(1);
COMMIT;
SELECT * FROM A;
--创建表B--
CREATE TABLE B (ID3 NUMBER, ID2 NUMBER(10));
INSERT INTO B VALUES(100,10);
INSERT INTO B VALUES(200,20);
COMMIT;
SELECT * FROM B;
--创建表C--
CREATE TABLE C (ID4 NUMBER, ID2 VARCHAR2(10));
INSERT INTO C VALUES(3,‘10‘);
INSERT INTO C VALUES(4,‘20‘);
COMMIT;
SELECT * FROM C;
--CTAS方式赋值employees表到EMP，带数据--
CREATE TABLE EMP AS SELECT * FROM employees WHERE 1=1; --将employees的数据和表结构复制到EMP表，WHERE子句可有可无。
SELECT * FROM EMP;
--CTAS方式赋值employees表到EMP1，仅数据结构，无数据--
CREATE TABLE EMP1 AS SELECT * FROM employees WHERE 1=2; --1=2不成立，所以没有数据。
SELECT * FROM EMP1;
DESC EMP1;
--CRATE TABLE XXX AS SELECT ... --> CTAS建表方式，不需要提交，属于DDL语言范畴。
使用连接从多个表中获得数据的语法
创建 Natural Joins（自然连接）
- NATURAL JOIN子句基于两个表中具有相同名称的所有列。
- 它从两个表中选择相同名称的列具有相同值的行作为有效数据。
- 如果具有相同名称的列具有不同的数据类型—会发生隐式数据类型转换。
SELECT employee_id, first_name, job_id, job_title FROM employees NATURAL JOIN jobs ORDER BY 1; EMPLOYEE_ID FIRST_NAME JOB_ID JOB_TITLE ----------- -------------------- ---------- ----------------------------------- 100 Steven AD_PRES President 101 Neena AD_VP Administration Vice President 102 Lex AD_VP Administration Vice President 103 Alexander IT_PROG Programmer 104 Bruce IT_PROG Programmer 105 David IT_PROG Programmer 106 Valli IT_PROG Programmer 107 Diana IT_PROG Programmer 108 Nancy FI_MGR Finance Manager 109 Daniel FI_ACCOUNT Accountant 110 John FI_ACCOUNT Accountant
employees表和departments表有两个相同的字段。
对于他们的自然连接，需要两个同名字段相同的数据都相等，才能作为有效的记录放到新的连接表中。
SELECT * FROM employees NATURAL JOIN departments; --最后得到的记录数为32条。
DESC employee;
名称 空值? 类型 -------------- -------- ------------ MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
DESC departments;
名称 空值? 类型 --------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) MANAGER_ID NUMBER(6)
对于我们自己创建的数据库B和数据库C的数据结构如下：
数据库B:
名称 空值? 类型
--- --- ----------
ID3 NUMBER
ID2 NUMBER(10)
数据库C:
名称 空值? 类型
--- --- ------------
ID4 NUMBER
ID2 VARCHAR2(10)
他们有相同的ID2，但是数据类型不同。 在做自然连接的时候，ID2的字段会自动进行隐式数据转换，从而将数据匹配起来。
SELECT * FROM B NATURAL JOIN C; ID2 ID3 ID4 ---------- ---------- ---------- 10 100 3 20 200 4
使用USING子句创建链接
- 查询必须是等值连接，不等值就不链接了，就不输出了。
- 等值连接中的列必须具有相同的名称，最好数据类型一致（有可能会遇到列名相同，但数据类型不同时，会发生隐式数据类型转换）
- USING子句可以含多个列，也可以只使用其中的一个列。
- USING(X)中的列不可以接表名或别名等限定语，默认就是指共同列，不能指定表名
SELECT employee_id, last_name, department_id, department_name
FROM employees JOIN departments
USING (department_id);
--也可以同时匹配两个字段，只有两个字段的值都相同的记录才会显示出来。
SELECT employee_id, last_name, department_id, department_name
FROM employees JOIN departments
USING (department_id, manager_id);
与自然连接和USING子句链接等效的SQL语句
SELECT employee_id, last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND employees.manager_id = departments.manager_id;
限定不明确的列名
- 使用表前缀来限定多个表中的列名： employees和departments都有department_id，可使用employees.department_id。
- 使用表别名代替完整的表名前缀： 比如： FROM employees E, 之后可以使用E.department_id。
- 使用列别名来区分名称相同但位于不同表中的列。SELECT E.department_id FROM employee E
SELECT last_name, salary,department_id, d.department_name
FROM employees E JOIN departments D
USING (department_id);
使用ON子句连接
与JOIN的固定搭配，可以指定指定要连接的列。
SELECT employee_id, last_name, E.job_id, job_title FROM employees E JOIN jobs J ON E.job_id = J.job_id; 等效于 SELECT employee_id, last_name, E.job_id, job_title FROM employees E , jobs J WHERE E.job_id = J.job_id;
将附加条件应用于ON字句连接
第一种写法 JOIN...ON...AND
SELECT employee_id, last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 100; --使用department_id关联employees和departments表，并且只保留mamager_id=100的记录。
第二种写法JOIN...ON...WHERE
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 100;
创建三表连接
SELECT employee_id, last_name, department_name, city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id;
自连接
同一张employee表，员工的manager，也是employee中的一条员工记录，创建自连接，来体现出员工以及他的经理。
SELECT emp.last_name "员工", emp.salary "员工工资", mgr.last_name "经理", mgr.salary "经理工资" FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id;
非等式连接
连接的条件不是等式，而是大于、小于、大于等于、小于等于或者BETWEEN...AND的区间范围。
新增一个表，叫做job_class，来把员工的工资分为三类。
CREATE TABLE JOB_CLASS (CLASS CHAR(10), LOW NUMBER, HIGH NUMBER);
INSERT INTO job_class VALUES (‘A‘,1000,9999);
INSERT INTO job_class VALUES (‘B‘,10000,19999);
INSERT INTO job_class VALUES (‘C‘,20000,29999);
使用非等式连接建立关联，employee表的工资字段对比job_class表的工资范畴来对应上相应的工资class。
SELECT employee_id, last_name, salary, class FROM employees e JOIN job_class j ON e.salary BETWEEN j.low AND j.high;
内连接Inner Join
内连接只输出相等值的结果，不输出不匹配值作为结果的一部分。
只写JOIN关键字默认就是内连接。
内连接的三种写法：
SELECT * FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT * FROM employees e, departments d WHERE e.department_id = d.department_id;
外连接Outer Join
左右外连接：返回INNER联接的结果以及左（或右）表中不匹配的行的两个表之间的联接称为左（或右）OUTER联接。
完全外连接：返回一个内部联接的结果以及一个左右联接的结果，是一个完整的外部联接。
对于左右连接，想要完整输出的表，叫做驱动表，与之连接的表，叫做匹配表。
--左外连接，Employees作为驱动表的时候，驱动表178号员工无department_id一样列印出来。
SELECT *
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
--右外连接，Employees作为驱动表的时候，employees表中没有用到的department_id一样列印出来。
SELECT *
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
输出两张表所有的匹配和不匹配的数据就用到全外连接。
SELECT * FROM employees e FULL JOIN departments d ON e.department_id = d.department_id ORDER BY employee_id desc;
笛卡尔积 Cross Join
笛卡尔积并不是按照某个字段匹配，而是直接将左边表的所有行和右边表的第一行进行连接，然后左边表的所有行和右边表的第二行进行连接，以此类推。
笛卡尔积会产生大量行，并且结果很少有用。
左边表m行，右边表n行，笛卡尔积会产生m*n行数据。
SELECT *
FROM employees e CROSS JOIN departments d;