Overview of JOIN types

JOIN types in FROM clause specify the operations performed on the virtual table

 Join Type
 Description
 Cross
Combines all rows in both tables (creates Cartesian product).
 Inner
Starts with Cartesian product; applies filter to match rows between tables based on predicate.
 Outer
Starts with Cartesian product; all rows from designated table preserved, matching rows from other table retrieved. Additional NULLs inserted as placeholders.

Understanding INNER JOINS

Returns only rows where a match is found in both tables
Matches rows based on attributes supplied in predicate
    ON clause in SQL-92 syntax

Why filter in ON clause?
     Logical separation between filtering for purposes of JOIN and
     filtering results in WHERE
     Typically no difference to query optimizer
If JOIN predicate operator is =, also known as equi-join

INNER JOIN Syntax

List tables in FROM Clause separated by JOIN operator Table order does not matter, and aliases are preferred

FROM   t1  JOIN  t2
      ON   t1 . column  =  t2 . column


SELECT   SOH.SalesOrderID,
                  SOH.OrderDate,
                  SOD.ProductID,
                  SOD.UnitPrice,
                  SOD.OrderQty
FROM  Sales.SalesOrderHeader  AS  SOH
JOIN    Sales.SalesOrderDetail   AS  SOD
ON   SOH.SalesOrderID  = SOD.SalesOrderID;

Understanding OUTER JOINS

Returns all rows from one table and any matching rows from second table

One tables's rows are "preserved"
    Designated  with  LEFT, RIGHT, FULL keyword
    All  rows from preserved table output to result set
Matches  from  other  table  retrived
Additional  rows added to results for non-matched  rows
    NULLs  added in place where attributes do not match

Example: Return all customers and for those who have place orders,
return order information. Customers  without  matching  orders  will  display NULL for order details.

OUTER JOIN examples

Customers  that did not placr orders:

SELECT  CUST.CustomersID,  CUST.StoreID,
ORD.SalesOrderID,  ORD.OrderDate
FROM  Sales.Customer  AS  CUST
LEFT  OUTER  JOIN  Sales.SalesOrderHeader   AS  ORD
ON  CUST.CustomerID  =  ORD.CustomerID
WHERE  ORD.SalesOrderID   IS  NULL ;

Understanding  CROSS  JOINS

Combine each row from first table with each row from second tables
All possible  combinations  are  displayed
Logical  foundation  for inner and  outer  joins
    INNER  JOIN  starts with  Cartesian  product, adds  filter
    OUTER  JOIN  takes Cartesian  output, filtered, adds back non-matching rows (with NULL       
    placeholders)

Due  to Cartesian product output,  not typically a desired form  of  JOIN  some useful  exceptions:
        Generating  a  table  of numbers for testing

CROSS JOIN  Example

Create test data by returning all combinations of two inputs:

SELECT  EMP1.BussinessEntityID,  EMP2.JobTitle
FROM  HumanResources.Employee  AS  EMP1
CROSS  JOIN  HumanResources.Employee  AS  EMP2 ;

Understanding  Self-Joins

Why use self-joins?
     Compare rows  in same table to each other
     Create two instances of same table in FROM clause
     At least one alias  required



Example: Return all employees  and  the name of the employee's manager














Self-Join examples

Return all employees with ID of  employee's  manger when a manager  exists (INNER JOIN)

SELECT   EMP . EmpID ,  EMP . LastName ,
                 EMP . JobTitle ,  EMP . MgrID ,  MGR . LastName
FROM      HR . Employees  AS  EMP
INNER  JOIN  HR . Employees  AS  MGR
ON  EMP . MgrID  =  MGR.EmpID ;

Return all employees with ID of  manager  (OUTER JOIN).  This will return  NULL   for the CEO:

SELECT  EMP . EmpID ,  EMP . LastName ,
                  EMP . Title ,  MGR . MgrID
FROM   HumanResources . Employee  AS EMP
LEFT  OUTER  JOIN   HumanResources . Employee  AS MGR
ON   EMP . MgrID  =  MGR . EmpID ;