ORACLE JOIN 与 +
JOIN：
1. Natural join：自然连接，ORACLE 自动根据两表中类似的列进行连接；
2. Outer join：Left outer join、Right outer join、Full outer join
Left outer join：from 后面的表显示完整；
Right outer join：join 后面的表显示完整；
Full outer join：两边均显示完整；
多表查询中如果缺少数据的列补充(+)，如果改写成left outer join则是相反的。
例如：
有两个表employees，departments
如果是departments中的department_id少
则写法如下：
SELECT last_name, department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department = d.department_id);
例如：
有两个表employees，departments
如果是departments中的department_id少
则写法如下：
SELECT last_name, department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department = d.department_id);
SELECT last_name, department_name
FROM employees e,departments d
WHERE e.department = d.department_id(+);
FROM employees e,departments d
WHERE e.department = d.department_id(+);
3. Cross join：笛卡尔积
Assuming you're joining on columns with no duplicates, which is by far the most common case:
- An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
- An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.
Examples
Suppose you have two Tables, with a single column each, and data as follows:
A B- -1 32 43 54 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;select a.*,b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;select a.*,b.* from a,b where a.a =b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Full outer join
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
Comments
Post a Comment