《oracle数据库》多表查询

一、笛卡尔集

笛卡尔集会在以下条件下产生：

1. 省略连接条件
2. 连接条件无效
3. 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。

select count(employee_id)
from employees;

假设输出107行

select count(department_id)
from departments;

假设输出27行

select last_name, department_name
from employees, departments;

结果输出107*27=2889行

二、Oracle连接

使用连接在多个表中查询数据。

• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀或表的别名加以区分。

1.等值连接

SELECT 	employees.employee_id, employees.last_name,
	employees.department_id, departments.department_id,
	departments.location_id
FROM   	employees, departments
WHERE  	employees.department_id = departments.department_id;

2.表的别名

SELECT 	e.employee_id, e.last_name, e.department_id,       	
	d.department_id, d.location_id
FROM   	employees e , departments d
WHERE 	e.department_id = d.department_id;

3.不等值连接

EMPLOYEES表中的列工资在JOB_GRADES表中的最高工资与最低工资之间：

SELECT 	e.last_name, e.salary, j.grade_level
FROM   	employees e, job_grades j
WHERE  	e.salary BETWEEN j.lowest_sal AND j.highest_sal;

4.连接多个表

连接 n个表,至少需要 n-1个连接条件。

查询出公司员工的 last_name, department_name, city（三个表，两个连接条件）：

SELECT 	last_name, department_name, city
FROM   	employees,departments,locations
WHERE 	employees.DEPARTMENT_ID = departments.DEPARTMENT_ID
AND 	departments.Location_Id = locations.Location_Id

5.外连接

连接条件中匹配行的表的列后面要加外连接运算符(+)

（1）左外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column = table2.column(+);

（2）右外连接

与左外连接相反，两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行 ，这种连接称为右外连接。

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column(+) = table2.column;

三、SQL: 1999 语法连接

1.CROSS JOIN：叉集（所有表中的所有行互相连接）

使用CROSS JOIN子句使连接的表产生叉集。
与笛卡尔集是相同的。

SELECT 	last_name, department_name
FROM   	employees CROSS JOIN departments ;

2.NATURAL JOIN：等值连接（自动匹配表中相同的列）

NATURAL JOIN子句，会以两个表中具有相同名字的列为条件创建等值连接。
在表中查询满足等值条件的数据。
如果只是列名相同而数据类型不同，则会产生错误。

SELECT 	department_id, department_name,       	
	location_id, city
FROM   	departments NATURAL JOIN locations ;

3.USING：指定匹配表中相同的列

使用 USING 子句指定等值连接中需要用到的列。
使用 USING 可以在有多个列满足条件时进行选择。
不要给选中的列中加上表名前缀或别名。
JOIN和USING 子句经常同时使用。

SELECT 	department_id, department_name,       	
	location_id, city
FROM  	departments JOIN locations USING(location_id);

4.ON：指定额外的连接条件

是以具有相同名字的列为连接条件的。
这个连接条件是与其它条件分开的。

SELECT 	department_id, department_name,       	
	d.location_id, city
FROM  	departments d JOIN locations l
ON 	d.location_id = l.location_id;

多表连接

SELECT 	employee_id, city, department_name
FROM  	employees e 
JOIN   	departments d
ON     	d.department_id = e.department_id 
JOIN   	locations l
ON     	d.location_id = l.location_id;

5.外连接

（1）LEFT OUTER JOIN：左外连接

SELECT 		e.last_name, e.department_id, d.department_name
FROM   		employees e
LEFT OUTER JOIN	departments d
ON  		(e.department_id = d.department_id);

（2）RIGHT OUTER JOIN：右外连接

SELECT 			e.last_name, e.department_id, d.department_name
FROM  			employees e
RIGHT OUTER JOIN	departments d
ON 			(e.department_id = d.department_id);

（3）FULL OUTER JOIN：满外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为满外连接。

SELECT 		e.last_name, e.department_id, d.department_name
FROM   		employees e
FULL OUTER JOIN	departments d
ON   		(e.department_id = d.department_id);