Joins

Join is a concept in relational database theory.

• A join is a technique for accessing data from more than one table in a single answer set.
• Each row in the answer set may contain data from columns of more than one table.
• Tables should be joined on columns sharing common domains—rows that have identical values in the join column are matched up.
• Joins can be used to make Views of data from more than one table.

Types of Joins

Inner Rows which match based on join criteria
Outer Inner join rows and remaining unmatched rows
Cross Each row of one table matched with each row of another
Self    Rows matching other rows within the same table

Inner Joins
To get a report that includes employee number, last name, and department name, join the employee table and the department table.

Below diagram is the Relational structures of EMPLOYEE and DEPARTMENT tables.

SELECT employee.employee_number 
,employee.last_name
, department.department_name
FROM employee INNER JOIN
department
ON employee.department_number = department.department_number ;

employee_number     last_name department_name
1006                         Stein          research and development
1008                         Kanieski     research and development
1005                         Ryan          education
1004                         Johnson      customer support
1007                         Villegas      education
1003                         Trader        customer support

The same Query also can be written as below with alias names and it gives the same results.

SELECT e.employee_number 
,e.last_name
,d.department_name
FROM employee e INNER JOIN
department d
ON e.department_number = d.department_number ;

Note:  Only columns with identical names need to be qualified.

Inner Joins On Multiple Tables

SELECT e.last_name 
,d.department_name
,j.description
FROM employee e       INNER JOIN
department d
ON e.department_number=d.department_number 
INNER JOIN
job j
ON e.job_code=j.job code;

last_name department_name description
Daly            software support  Manager - Software Supp
Runyon marketing sales        Manager - Marketing Sale
Trainer president Corporate  President
Brown customer support Dispatcher
 …                …                          …

Cross Joins

• A cross join is a join that requires no join condition.
• Cross joins are sometimes called product joins.

SELECT e.employee_number
,d.department_number
FROM employee e CROSS JOIN
department d
WHERE e.employee_number = 1008;

employee_number department_number
1008                         301
1008                         501
1008                         402
1008                         201
1008                          302
1008                          600
1008                          401
1008                          100
1008                          403

Cartesian Products 

• A completely unconstrained cross join is called a Cartesian product.
• Each row of one table is joined to each row of another table.
• A Cartesian product results from a CROSS JOIN without a WHERE clause.
• Cartesian products can also result from an INNER JOIN with improper aliasing or improper join conditions.

SELECT employee.employee_number
,employee.department_number
FROM employee CROSS JOIN
department;

Self Joins

A self join combines the information from two or more rows of the same table into a single result row, effectively joining the table to itself

SELECT emp.first_name (TITLE 'Emp//First Name')
,emp.last_name (TITLE 'Emp//Last Name)
,mgr.first_name (TITLE 'Mgr//First Name')
,mgr.last_name (TITLE 'Mgr//Last Name')
FROM employee emp INNER JOIN
employee mgr
ON emp.manager_employee_number  =mgr.employee_number
WHERE emp.last_name  =  'Brown’;

Emp                 Emp            Mgr         Mgr 
First Name Last Name    First Name Last Name
Allen                 Brown    Loretta         Ryan
Alan                 Brown    James         Trader


Outer Joins:

Start with an INNER JOIN and convert to an OUTER JOIN.
Change the appropriate INNER descriptors to LEFT OUTER, RIGHT OUTER or FULL OUTER join based on the desire to include the exception rows. Since INNER and OUTER joins can be used together, one join at a time can be changed to validate the output.

An Outer Join returns Qualifying rows and Non-Qualifying rows.

LEFT OUTER JOIN
Table to the left is used to Qualify, Table on the right has nulls when rows do not match.

SELECT e.employee_number 
,e.last_name
,d.department_name
FROM employee e LEFT OUTER JOIN
department d
ON e.department_number = d.department_number ;

RIGHT OUTER JOIN
Table to the right is used to Qualify, Table on the left has nulls when rows do not match.
                                      
SELECT e.employee_number 
,e.last_name
,d.department_name
FROM employee e RIGHT OUTER JOIN
department d
ON e.department_number = d.department_number ;

FULL OUTER JOIN
Both tables are used to Qualify and extended with nulls

SELECT e.employee_number 
,e.last_name
,d.department_name
FROM employee e      FULL OUTER JOIN
department d
ON e.department_number = d.department_number ;

Outer Joins on Multiple tables.

SELECT e.last_name 
,d.department_name
,j.description
FROM employee e RIGHT OUTRT JOIN
department d
ON e.department_number=d.department_number 
LEFT OUTRT JOIN
job j
ON e.job_code=j.job code;