Join
A join is a
query that combines rows from two or more tables, views, or materialized views.
Oracle Database performs a join whenever multiple tables appear in the FROM
clause of the query.
Most join
queries contain at least one join condition, either in the FROM clause or in
the WHERE clause. The join condition compares two columns, each from a
different table. To execute a join of three or more tables, Oracle first joins
two of the tables based on the join condition comparing their columns and then
joins the result to another table based on join conditions containing columns
of the joined tables and the new table.
Equijoins:
An equijoin
is a join with a join condition containing an equality operator(=). An equijoin
combines rows that have equivalent values for the specified columns.
Ex. SELECT
empno, ename, job, dname, loc FROM emp e, dept d WHERE e.deptno=d.deptno;
Non-Equijoins:
A join which
contains an operator other than equal to ‘=’ in the joins condition.
Ex. SELECT
empno, ename, job, dname, loc FROM emp e, dept d WHERE e.deptno>d.deptno;
Self Joins:
A self join
is a join of a table to itself. This table appears twice in the FROM clause and
is followed by the table aliases that qualify column names in the join
condition.
Ex. SELECT
e1.empno, e2.ename, e1.job, e2.deptno FROM emp e1, emp e2 WHERE
e1.empno=e2.mgr;
Inner Joins:
An inner join
(sometimes called a simple join) is a join of two or more tables that returns
only those rows that satisfy the join condition.
Ex. SELECT
empno, ename, job, dname, loc FROM emp INNER JOIN dept USING(deptno);
Outer Joins:
An outer join
extends the result of a simple join. An outer join returns all rows that
satisfy the join condition.
To write a
query that performs an outer join of tables A and B and returns all rows from A
(a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the
outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in
A that have no matching rows in B, Oracle Database returns null for any select
list expressions containing columns of B.
Ex. SELECT
empno, ename, job,dname,loc FROM emp e LEFT OUTER JOIN dept d
on(e.deptno=d.deptno);
OR
SELECT empno,
ename, job, dname, loc FROM emp e, dept d WHERE e.deptno=d.deptno(+);
To write a
query that performs an outer join of tables A and B and returns all rows from B
(a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the
outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in
B that have no matching rows in A, Oracle returns null for any select list
expressions containing columns of A.
Ex. SELECT
empno, ename, job, dname, loc FROM emp e RIGHT OUTER JOIN dept d
ON(e.deptno=d.deptno); OR
SELECT empno,
ename, job, dname, loc FROM emp e, dept d WHERE e.deptno(+)=d.deptno;
To write a
query that performs an outer join and returns all rows from A and B, extended
with nulls if they do not satisfy the join condition (a full
outer join), use the FULL
[OUTER] JOIN syntax in the FROM clause.
Ex. SELECT
empno, ename, job, dname, loc FROM emp e FULL OUTER JOIN dept d
ON(e.deptno=d.deptno);
