SQL Joins | Equi Join | Inner Join | Non-equi Join | Outer Joins | Self Joins |

 

Joins

What are Joins?

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.

The select list of the query can select any columns from any of these tables.

If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Types of Joins:

• Equi-Join / Inner Join
• Non-Equi Join
• Self Join
• Outer Join

Joining Tables Using Oracle Syntax

When data from more than one table in the database is required, a join condition is used.

Rows in one table can be joined to rows in another table according to common values existing in corresponding columns

To display data from two or more related tables, write a simple join condition in the WHERE clause.

Syntax:

                 SELECT table1.column, table2.column

                 FROM table1, table2

                 WHERE table1.column1 = table2.column2;

In the syntax:

   table1.column denotes the table and column from which data is retrieved

   table1.column1 = is the condition that joins (or relates) the tables together table2.column

 

Equi join

What is Equi join?

An equi join is a join with a join condition containing an equality operator. An equi join combines rows that have equivalent values for the specified columns.

Retrieving Records:

SELECT EMP.EMPNO, EMP.ENAME,

EMP.DEPTNO, DEPT.DEPTNO,DEPT.LOC

FROM   EMP, DEPT

WHERE  EMP.DEPTNO = DEPT.DEPTNO;

 

Using Table Aliases

Simplify queries by using table aliases.

Improve performance by using table prefixes.

SELECT e.EMPNO, e.ENAME, e.DEPTNO,

d.DEPTNO, d.LOC

FROM   EMP e , DEPT d

WHERE  e.DEPTNO = d.DEPTNO;

 

Non-Equijoins

SELECT e.ENAME, e.SAL, j.GRADE

FROM   EMP e, SALGRADE j

WHERE  e.SAL

BETWEEN j.LOSAL AND j.HISAL;

Outer joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other 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.

•       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.

•       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.

 

 

• You use an outer join to also see rows that do not meet the join condition from one table.

• The Outer join operator is the plus sign (+).

                           SELECT table1.column, table2.column

                                          FROM table1, table2

                                          WHERE  table1.column(+) = table2.column;

 

                          SELECT table1.column, table2.column

                                         FROM table1, table2

                                         WHERE  table1.column = table2.column(+);

     

     Example : SELECT e.ENAME, e.DEPTNO, d.DNAME

                                      FROM   EMP e, DEPT d

                                      WHERE  e.DEPTNO(+) = d.DEPTNO ; 

Self joins

• Joining to a table itself.

    Example : SELECT  worker.ENAME || ' works for '  || manager.ENAME

                                      FROM  EMP worker, EMP manager

                                      WHERE  worker.MGR = manager.EMPNO;