SQL JOINS Exmaples

Example Tables

Employee table

LastName        DepartmentID (FroeignKey)

Rafferty 31

Jones            33

Heisenberg 33

Robinson 34

Smith            34

John            NULL

Department table

DepartmentID DepartmentName

31                        Sales

33                        Engineering

34                        Clerical

35                       Marketing

SELECT *

FROM employee JOIN department

  ON employee.DepartmentID = department.DepartmentID;

We can write equi-join as below,

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID;

Inner Join Result           ensures No Null values be matched

LastName   empid/depttid DepartmentName

Rafferty   31     Sales

Jones      33     Engineering

Heisenberg 33     Enginnering

Robinson   34     Clerical

Smith      34     Clerical

SELECT *

FROM employee LEFT OUTER JOIN department

  ON employee.DepartmentID = department.DepartmentID;

Alternative syntax

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID(+)

Outer Join Result            may contain Null values

LastName   empid/deptid     DepartmentName 

Rafferty             31   Sales

Jones                  33   Engineering

Heisenberg      33   Enginnering

Robinson          34    Clerical

Smith                  34    Clerical

John              NULL   NULL

Note outer Join  A left outer join returns all the values from an inner join plus all values in the left 

table that do not match to the right table.

Right outer Join 

SELECT *

FROM employee RIGHT OUTER JOIN department

  ON employee.DepartmentID = department.DepartmentID;

LastName   empid/deptid    DepartmentName 

Rafferty             31   Sales

Jones                 33   Engineering

Heisenberg     33   Enginnering

Robinson         34    Clerical

Smith                34    Clerical

NULL           NULL Marketing

Note

A right outer join returns all the values from the right table and matched values from the left table 

If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records 

that have no match in B.

Now Full outer Join combines both Left and right outer join results

SELECT *

FROM employee FULL OUTER JOIN department

  ON employee.DepartmentID = department.DepartmentID;

LastName   empid/deptid     DepartmentName 

Rafferty           31   Sales

Jones                33   Engineering

Heisenberg    33   Enginnering

Robinson        34            Clerical

Smith               34            Clerical

NULL           NULL Marketing

John           NULL   NULL