Joins

A SQL join clause combines records from two or more tables in a relational database, resulting in a new, temporary table, sometimes called joined table. It relates the tables by means of values common between them.

Types of Join

    
- Inner join

        - Equi-join

            - Natural join

        - Cross join

- Outer join

    - Left Outer join

    - Right Outer join

    - Full Outer join

Inner join

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate.

Eg:

SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID

Equi join

is a specific type of comparator-based join, that uses only equality comparions in the join condition

Natural join

The join condition arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.

Eg:

SELECT *
FROM employee NATURAL JOIN department

Cross or Cartesian join

A cross join return the cartesian product of the sets of records from the two joined tables. It equates to an inner join where the join-condition always evaluates to true or join condition is absent in statement.

Eg:

SELECT *
FROM employee CROSS JOIN department

SELECT *
FROM employee, department;

Outer joins

An outer join does not require each record in the two tables to have a matching record. The joined table retains each record - even if no other matching record exists. Outer joins subdivided further into left outer joins, right outer joins, depending on which table(s) one retains the rows from (left, right or both)

Left Outer join

A leftouter join returns all the values from the left table and matched values from the right table. (NULL in case of no matching)

Eg:

SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT * FROM employee, department

where employee.DepartmentID (+) = department.DepartmentID

Right outer join

A right outer join returns all the values from the right table and matched values from the left table. (NULL in case of no matching)

Eg:

SELECT *

FROM employee LEFT OUTER JOIN department

ON employee.DepartmentID = department.DepartmentID

SELECT * FROM employee, department
where employee.DepartmentID (+) = department.DepartmentID

Full outer join

A full outer join combines the result of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Eg:

SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID

SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM employee
RIGHT JOIN department
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL

SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM department
LEFT JOIN employee
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL

SELECT *

FROM department RIGHT JOIN employee

ON employee.DepartmentID = department.DepartmentID

UNION

SELECT *

FROM employee RIGHT JOIN department

ON employee.DepartmentID = department.DepartmentID

WHERE employee.DepartmentID IS NULL