一、笛卡尔集
笛卡尔集会在以下条件下产生:
- 省略连接条件
- 连接条件无效
- 所有表中的所有行互相连接
为了避免笛卡尔集, 可以在 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);