oracle中的左右连接

在oracle中左右连接省去了sql server中复杂的连接语句(left join ,right join)，就用一个"(+)"表示。
下面做了下实验：其中查询1和查询2是等同效果的，查询3和查询4是等同效果的。

查询1:
select first_name,department_name,emp.department_id from emp,departments dept
where emp.department_id(+)=dept.department_id;

122 rows selected.

查询2:
select first_name,department_name,emp.department_id from departments dept left join emp
on emp.department_id=dept.department_id;

122 rows selected.

 

 

查询3:
select first_name,department_name,emp.department_id from emp,departments dept
where emp.department_id=dept.department_id(+);

107 rows selected.

查询4:
select first_name,department_name,emp.department_id from  emp  left join departments dept
on emp.department_id=dept.department_id;

107 rows selected.

 

总结：

1，（+）在哪一边，则返回另一边所有的记录。

2，（+）放在包含空值的一边，不可以两边同时使用。

 

 

补充内联的写法即用using关键词：

select first_name,department_name,department_id from emp join departments dept using(department_id);

注意：USING里的字段不能带别名，SELECT_LIST里的USING里的字段也不能带别名，看下面的两个例子：

select first_name,department_name,emp.department_id from emp join departments dept using(emp.department_id);
using(emp.department_id)
         *
ERROR at line 2:
ORA-01748: only simple column names allowed here

select first_name,department_name,emp.department_id from emp join departments dept using(department_id);
select first_name,department_name,emp.department_id from emp join departments dept
                                  *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

 

join using他相当于inner join

select emp.first_name,dept.department_name,emp.department_id from emp join departments dept
on emp.department_id=dept.department_id;