join

PLSQL 连接查询

符合SQL:1999 标准的连接包括：
• 自然连接：
– NATURAL JOIN子句
– USING子句
– ON子句
• OUTER连接：
– LEFT OUTER JOIN
– RIGHT OUTER JOIN
– FULL OUTER JOIN
• 交叉连接
语法：
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];
在该语法中：
• table1.column表示从中检索数据的表和列
• NATURAL JOIN根据相同的列名连接两个表
• JOIN table2 USING column_name根据列名执行等值连接
• JOIN table2 ON table1.column_name = table2.column_name根据
ON子句中的条件执行等值连接
• LEFT/RIGHT/FULL OUTER用于执行OUTER连接
• CROSS JOIN用于返回两个表的笛卡尔积
使用表前缀可以提高性能，因为这等于告知Oracle Server 查找这些列的确切位置。
表别名是表的短名称：使SQL 代码变得更短，因而占用更少的内存
NATURAL JOIN
可以根据两个表中具有相匹配的数据类型和名称的那些列，对表执行自动连接。使用
NATURAL JOIN关键字可以完成此操作。如果存在其它通用列，连接也会使用所有这些列。
注：只能对两个表中具有相同名称和数据类型的那些列执行连接。如果列的名称相同但
数据类型不同，那么NATURAL JOIN语法将导致产生一个错误。
select d.department_id, d.department_name, location_id, l.city
from departments d natural
join locations l
where d.department_id in (20, 50);
使用USING子句创建连接
• 如果多个列具有相同的名称，但数据类型不匹配，使用USING子句指定等值连接的列。
• 当有多个列相匹配时，使用USING子句可仅与一列相匹配。
• NATURAL JOIN和USING语句是互相排斥的。
select e.employee_id, e.first_name || e.last_name, d.department_name
from employees e
join departments d
using (department_id)
where department_id = 20;
使用ON子句创建连接
• 自然连接的基本连接条件是对具有相同名称的所有列进行等值连接。
• 使用ON子句可指定任意条件或指定要连接的列。
• 连接条件独立于其它搜索条件。
• 使用ON子句可使代码易于理解。
select e.employee_id, e.last_name, d.department_name, l.city
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
and e.employee_id = 198;
小结：
NATURAL JOIN、USING子句、ON子句之间的区别
NATURAL JOIN：根据两个表中具有相匹配的数据类型和名称的那些列（可能是多列）进行连接
USING子句：当有多个列相匹配时，使用USING子句可仅与一列相匹配。
ON子句：可以用来连接名称不同但数据类型相同的列。
等值连接也称为简单连接或内部连接（INNER JOIN）。
自连接
查询每位员工对应的经理：
select worker.last_name emp, manager.last_name mgr
from employees worker
join employees manager
on worker.manager_id = manager.employee_id;
非等值连接
select e.last_name, e.salary, j.grade_level
from employees e
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;
INNER连接与OUTER连接
• 在SQL:1999 中，如果两个表的连接只返回相匹配的行，则称该连接为INNER连接。
• 如果两个表之间的连接不仅返回INNER连接的结果，还返回左（或右）表中不匹配的行，则称该连接为左（或右）OUTER连接。
• 如果两个表之间的连接不仅返回INNER连接的结果，还返回左和右连接的结果，则称该连接为完全OUTER连接。
LEFT OUTER JOIN
此查询将检索EMPLOYEES表（它是左表）中的所有行，即使DEPARTMENTS表中没有
匹配项也是如此。
select e.last_name, e.department_id, d.department_name
from employees e
left join departments d
on e.department_id = d.department_id;
RIGHT OUTER JOIN
此查询将检索DEPARTMENTS表（它是右表）中的所有行，即使EMPLOYEES表中没有
匹配项也是如此。
select e.last_name, e.department_id, d.department_name
from employees e
right join departments d
on e.department_id = d.department_id;
FULL OUTER JOIN
此查询将检索EMPLOYEES表中的所有行，即使DEPARTMENTS表中没有匹配项也是如此。
它还检索DEPARTMENTS表中的所有行，即使EMPLOYEES表中没有匹配项也是如此。
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;
Oracle还有一种特有的连接写法("+"号表示连接)，RIGHT OUTER JOIN 的"+"在左边，LEFT OUTER JOIN的"+"在右边，总之"+"在哪一边，代表的连接方式为"+"号的反方向连接。
例如下面的连接方式为LEFT OUTER JOIN
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);
总结：
自然连接（INNER JOIN）是只显示满足条件的。
LEFT OUTER JOIN显示左边全部的和右边与左边相同的。
RIGHT OUTER JOIN显示右边全部的和左边与右边相同的。
FULL OUTER JOIN显示LEFT OUTER JOIN和RIGHT OUTER JOIN的合集。
笛卡尔积：返回左表中的每一行与右表中所有行的组合
• 出现以下情况时将形成笛卡尔积：
– 连接条件被忽略
– 连接条件无效
– 第一个表中的所有行被连接到第二个表中的所有行
• 如果要避免生成笛卡尔积，请始终包括有效的连接条件。
select e.last_name, d.department_name
from employees e
cross join departments d;