JOIN Commands

Joining Tables

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/li>
• 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

INNER JOIN

- finds matching data

SELECT buyer_name, sales.buyer_id, qty
  FROM buyers INNER JOIN sales
    ON buyers.buyer_id = sales.buyer_id

Using an alias:

SELECT buyer_name, s.buyer_id, qty
  FROM buyers AS b INNER JOIN sales AS s
    ON b.buyer_id = s.buyer_id

Old way to do INNER JOIN

SELECT buyer_name, buyer_id, qty
  FROM buyers AS b, sales AS s
    WHERE b.buyer_id = s.buyer_id

OUTER JOIN

- finds missing data

SELECT buyer_name, buyer_id, qty
  FROM buyers LEFT OUTER JOIN sales
    ON buyers.buyer_id = sales.buyer_id

Use a WHERE commmand to filter the results

SELECT buyer_name, buyer_id, qty
  FROM buyers LEFT OUTER JOIN sales
    ON buyers.buyer_id = sales.buyer_id
      WHERE qty IS NULL

Old way to do OUTER JOIN

SELECT buyer_name, buyer_id, qty
  FROM buyers, sales
    WHERE buyers.buyer_id = sales.buyer_id +
    -- The + is the LEFT OUTER JOIN

CROSS JOIN

- finds all combinations of data

SELECT buyer_name,qty
  FROM buyers 
    CROSS JOIN sales      

Joining more than two tables

SELECT buyer_name, prod_name, qty
  FROM buyers
  INNER JOIN sales
    ON buyers.buyer_id = sales.buyer_id
  INNER JOIN produce
    ON sales.prod_id = produce.prod_id

The old way to join more than two tables

SELECT buyer_name, prod_name, qty
  FROM buyers, sales, produce
    WHERE buyers.buyer_id = sales.buyer_id
      AND sales.prod_id = produce.prod_id

SELF JOIN

- joined to itself

SELECT managers.firstname, workers.firstname
  FROM payroll AS managers INNER JOIN payroll AS workers
    ON workers.manager_sin = managers.sin#
      ORDER BY 1,2
      -- numeric values in ORDER BY will sort by column 1 then column 2

UNION

JOIN creates a single result set from multiple queries

• Similar data types
• Same number of columns
• Same column order in SELECT list

SELECT (firstname + ' ' + lastname) AS Name,
        city, postalcode
  FROM employees
UNION
SELECT companyname, city, postalcode
  FROM customers