---1. Ordinary equal connection
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
---2 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);
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);
The result is: records of all employees and corresponding departments, including employee records that do not have a corresponding department number department_id.
----3. Right outer joinright 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);
select e.last_name, d.department_id,d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
The result is: records of all employees and corresponding departments, including department records without any employees.
----4.FULL OUTER JOIN: Full foreign association
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);
The result is: records of all employees and corresponding departments, including employee records without corresponding department number department_id and department records without any employees.
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/Phoenix_99/archive/2009/12/30/5107827.aspx