[Zway] SQL Notes for Chapter 2 - JOIN &UNION

Content

1. Inner Joins

2. Join Across Database

3. Self Join

 4. Joining Multiple Table

5. Compound Join Condition 

6. Implicit Join Syntax 

7. Outer Join

8. Outer Join Between Multiple Table

9. Self Outer Join 

10. The Using Clause

 11. Natural Joins

12. Cross Join

13. Unions

---

1. Inner Joins

Usually we select columns from one table, but when we need to select columns from multiple tables, we use 'INNER JOIN table2 ON ...same attribute...' (inner could be omited) to achieve that.

-- Instead of showing customer ID, full name dispaly in order.
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id

Excercise 1-1 

-- Show order quantity and unit price  in order items table with product name in product table
SELECT order_id, oi.product_id, quantity, oi.unit_price 
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
-- p.unit_price is tag price
-- oi.unit_price is instant price of each order
-- Price change all the time

2. Join Across Database

For joining tables columns from multiple database, just add database name in the front of table.

-- Join order table in sql_store with products table in sql_inventory
SELECT order_id, p.product_id, name, quantity, p.unit_price
FROM order_items oi
JOIN sql_inventory.products p ON oi.product_id = p.product_id

3. Self Join

Some table exists unary relationship. Like in emplyee table CEO manage staff. Give the same table different name 'FROM table1 a JOIN table 1 b ON.. unary attribute condition..'

USE sql_hr;
-- Return the name of employees and their manager
SELECT 
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id

 4. Joining Multiple Table

When you join across databse or self join, you may join more than 2 tables. Use several times of 'JOIN .(table 3).. ON .(equal condition)' to acheive joing multiple table.

USE sql_store;
-- Rturn order detials to customers with order id, customer name and order status
SELECT o.order_id, o.order_date, c.first_name, c.last_name, os.name AS status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON status = os.order_status_id

 Exercise 4-1

USE sql_invoicing;
-- Rturn payments table with client table and payment methods table to show the details
SELECT 
	p.invoice_id, 
    p.amount, 
    p.date, 
    c.name AS client_name, 
    pm.name AS payment_method
FROM payments p
JOIN clients c ON p.client_id = c.client_id
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id

 

5. Compound Join Condition 

Used when we have multiple condition to join two table. This usually happens when the two tables have the same compound primary key, and we use 'JOIN table2 ON ..condi1.. AND ..condi2..' to acheive compound join.

USE sql_store;
-- Join order items table with order item notes table (both of which have compound pk)
SELECT oi.order_Id, oi.product_id, oi.quantity, oi.unit_price, oin.note
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_Id
    AND oi.product_id = oin.product_id

 

6. Implicit Join Syntax 

Unlike use 'FROM table 1 Join table 2 ON condition' explicit join systax, 'FROM table 1, table 2 WHERE condition' implicit join syntax can also acheive inner join. However, it is not recommanded to be implicit since if u forget ' WHERE condition' u will get tons of records in results of cross join (n*m). 

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
-- Implicit Join Syntax
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

7. Outer Join

Outer Join is used when you want the attribute in condition list all the value in regard of the condition is true or not. 'LEFT/RIGHT JOIN table 2 ON condition' will work. Left or right is depended on table 1 or table 2.

-- Rturn all customers' order in regard of purchase or not
SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM orders o
LEFT JOIN customers c
	ON o.customer_id = c.customer_id
ORDER BY c.customer_id

Exercise 7-1

-- Join product table and order item table
SELECT 
	p.product_id,
    p.name, 
    oi.quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
ORDER BY product_id

 

8. Outer Join Between Multiple Table

When you join across databse or self join, you may join more than 2 tables. Use several times of 'LEFT/RIGHT JOIN .(table 3).. ON .(equal condition)' to acheive joing multiple table.

-- Outter join customers with order and shipper table
SELECT 
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.shipper_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

Exercise 8-1

-- Outter join order table with customers name and shippers name and status
SELECT 
	o.order_id, 
    o.order_date, 
    c.first_name, 
    sh.name AS shipper, 
    os.name AS status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON o.status = os.order_status_id
LEFT JOIN shippers sh ON sh.shipper_id = o.shipper_id
ORDER BY o.order_id

 ​​​​​​​

9. Self Outer Join 

Just as inner self join but change it to outer self join. 'FROM table1 a JOIN table1 b ON condition' 

-- Return all the employees id and name and thier manager name (Whether they have managers or not)
SELECT 
	e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.reports_to= m.employee_id

10. The Using Clause

'Using (attribute)' is an upgrade clasue of 'ON ..condition..'. It can only be used when attribute name is same among tables. Using Clause is usefull in Compound Join Condition

-- JOIN order customer shipper shipped status
SELECT 
	o.order_id,
    o.order_date,
    c.first_name,
    sh.name AS shipper,
    os.name AS status
FROM orders o
JOIN customers c USING (customer_id)
LEFT JOIN shippers sh USING (shipper_id)
JOIN order_statuses os ON o.status = os.order_status_id
ORDER BY o.order_id

-- JOIN order items with order item notes
SELECT *
FROM order_items oi
LEFT JOIN order_item_notes oin USING (order_id, product_id)

-- Show payments ditails with client name, date, amount and card name
SELECT 
	c.name AS client,
    p.date,
    p.amount,
    pm.name AS card_name
FROM payments p
JOIN clients c USING (client_id)
JOIN invoices i ON p.amount = i.payment_total
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id

 11. Natural Joins

Let the engine automatically join two tables based on common columns just by 'NATURAL JOIN table2'. However unexpected results might happen cuz you cannot contol the join. Avoid use it.

SELECT 
	o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

12. Cross Join

Every record (if # = n) in table 1 combine with every record (if # = m) in table2, returning n*m records in total. 'CROSS JOIN table2'

-- Return each customers all purchase items
SELECT
	c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name

 An implicit way to cross join is 'FROM table1, table2'

-- Do a cross join between shippers and products
-- Using implicit syntax
 SELECT 
	sh.name AS shipper,
    p.name AS product
FROM shippers sh, products p
ORDER BY sh.name
-- then using explicit syntax
 SELECT 
	sh.name AS shipper,
    p.name AS product
FROM shippers sh, 
CROSS JOIN products p
ORDER BY sh.name

13. Unions

 Combine rows from multiple tables and combine records from multiple quaries.'UNION'

-- Union active order with archived order
SELECT 
	order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
    order_date,
    'Archieve' AS status
FROM orders
WHERE order_date < '2019-01-01'

Also combine records from multiple quaries from different tables into one result set. However we should select the same number of attribute to union rows otherwsie it shows wrong.

SELECT name AS full_name
FROM shippers
UNION
SELECT first_name
FROM customers

-- Return customers by bronze((<2000), silver((2000-3000), gold(>3000)
SELECT 
	customer_id,
    first_name,
    points,
    'Bronze' AS status
FROM customers
WHERE points < 2000
UNION
SELECT 
	customer_id,
    first_name,
    points,
    'Silver' AS status
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT 
	customer_id,
    first_name,
    points,
    'Gold' AS status
FROM customers
WHERE points > 3000