Different Types of Joins-

Joins

A range of new join syntax are available that comply with the ANSI/ISO SQL: 1999 standards.

CROSS JOIN

The CROSS JOIN produces a cartesian product.

ANSI/ISO Syntax

select e.ename, d.dname
from emp e cross join dept d;

Existing Syntax
select e.ename, d.dname
from emp e , dept d ;

NATURAL JOIN

The NATURAL JOIN performs a join for all columns with matching names in the two tables.

ANSI/ISO Syntax

select e.ename, d.dname
from emp e natural join dept d;

Existing Syntax

select e.ename, d.dname
from emp e , dept d
where e.deptno = d.deptno;

JOIN ... USING

The USING clause is used if several columns share the same name, but you do not wish to join using all of these common columns. The columns listed in the USING clause cannot have any qualifiers in the statement, including the WHERE clause.


ANSI/ISO Syntax

select e.ename, e.job, d.dname
from emp e join dept d
using (deptno)

Existing Syntax

select e.ename, e.job, d.dname
from emp e , dept d
where e.deptno = d.deptno

Multiple Joins

Multiple Joins are those where more than two tables are joined

ANSI/ISO Syntax

select first_name, last_name, department_name, city
from employees e join departments d on (e.department_id = d.department_id)
join locations l on (d.location_id = l.location_id)

Existing Syntax

select first_name, last_name, department_name, city
from employees e , departments d , locations l
where e.department_id = d.department_id
and d.location_id = l.location_id

OUTER JOIN

There are three variations on the outer join. The LEFT OUTER JOIN returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist. The RIGHT OUTER JOIN does the reverse of this. Finally, the FULL OUTER JOIN returns all rows from both tables, filling in any blanks with nulls.


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
left outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

select l.city, d.department_name
from locations l , departments d
where l.location_id = d.location_id(+)


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
right outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

select l.city, d.department_name
from locations l , departments d
where l.location_id(+) = d.location_id


ANSI/ISO Syntax

select l.city, d.department_name
from locations l
full outer join
departments d
on (l.location_id = d.location_id)


Existing Syntax

No Equivalent!