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);