SQL JOIN Clauses

Intro

A JOIN is used to combine the data from different tables based on a related column between them

There are many different types of JOINs:

SET JOINs are used to join the same type of table

• SET JOINs
• UNION - combines all data
• INTERSECT - shows the common data
• EXCEPT - shows the differences between the tables

---

EQUI-JOINs are used for different types of tables

• EQUI-JOINs
• SELF - joined to itself
• CROSS - (USED IN BUSINESS INTELIGENCE) finds all combinations of data
• INNER - (MOST IMPORTANT JOIN) finds matching data
• OUTER - finds missing data
• LEFT - returns all records from left side and matching records from right side
• RIGHT - returns all records from right side and matching records from left side
• FULL - returns all records when there is a match in either left or right table

---

NESTED JOINs are used to solve complex query problems

Also called SUB-QUERIES
• NESTED JOINs
• CORRELATED - (SLOWEST QUERY)
• NON-CORRELATED -

Rules for joining tables:

• One of the sides MUST be a Primary Key
• Add data to parents first, then child tables - if there is no parent, you can't add data

Sample Data

These are the tables we will use as an example:

SELECT * 
FROM ar_invoices

SELECT * 
FROM ar_salespeople

SELECT * 
FROM ar_customers

INNER JOIN

Returns all the records that have matching data in both tables

Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name

SELECT salesperson_name,
       invoice_amount,
       customer#
FROM ar_invoices 
INNER JOIN ar_salespeople
    ON ar_invoices.salesperson# = ar_salespeople.salesperson#

Combine multiple JOINs to link many tables together:

SELECT salesperson_name,
       invoice_amount,
       customer_name
FROM ar_invoices 
INNER JOIN ar_salespeople
    ON ar_invoices.salesperson# = ar_salespeople.salesperson#
INNER JOIN ar_customers
    ON ar_invoices.customer# = ar_customers.customer#

The old way to do an INNER JOIN (ditch the keywords for commas and use a WHERE):

SELECT salesperson_name,
       invoice_amount,
       customer_name
FROM ar_invoices, ar_salespeople, ar_customers
    WHERE ar_invoices.salesperson# = ar_salespeople.salesperson#
    AND ar_invoices.customer# = ar_customers.customer#

LEFT OUTER JOIN

Returns all the data from the left table, and the matched records from the right table. If there is no match in the right side, the result is NULL

Syntax

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name

SELECT salesperson_name,
       invoice#
FROM ar_salespeople LEFT OUTER JOIN ar_invoices
    ON ar_invoices.salesperson# = ar_salespeople.salesperson#

A RIGHT OUTER JOIN is the same except it shows all the records from the right table

FULL OUTER JOIN

A FULL OUTER JOIN returns all the data from both tables

Note: FULL OUTER JOIN can potentially return huge result sets!

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition

SELECT salesperson_name,
       invoice#
FROM ar_salespeople FULL OUTER JOIN ar_invoices
    ON ar_invoices.salesperson# = ar_salespeople.salesperson#

SELF JOIN

A SELF JOIN is a regular join, except the table is joined to itself

Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition

This example finds all the customers that live in the same province

SELECT A.customer_name AS CustomerA,
       B.customer_name AS CustomerB,
	   A.customer_province
FROM ar_customers A,
     ar_customers B
WHERE A.customer# <> B.customer#
AND A.customer_province = B.customer_province

CROSS JOIN

A CROSS JOIN will link every column in the first table to every column in the second table

CROSS JOIN is used for business inteligence (research/data mining)

Syntax

SELECT column_name(s)
FROM table1
CROSS JOIN table2

SELECT salesperson_name,
       invoice_amount
FROM ar_salespeople    
CROSS JOIN ar_invoices

UNION JOIN

A UNION JOIN is used to combine the result-set of two or more SELECT statements

• Each SELECT statement within UNION must have the same number of columns
• Those columns must be of the same data types
• Those columns must also be in the same order

Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

SELECT salesperson_name AS Name
FROM ar_salespeople
UNION
SELECT customer_name
FROM ar_customers