Linking Scattered Data With Joins

Goal

You learn to choose and use inner joins, left outer joins, anti joins, and self joins as the situation requires, and to explain the difference between the ON clause and the WHERE clause.

Why it matters

There is in fact only one thing to decide in a join: whether to keep or discard rows that have no partner. If you do not make this decision clear, report numbers are silently wrong.

An especially dangerous point is the WHERE clause after a left outer join. If you put a condition on a column of the right table, the rows that were filled with NULL because they had no partner become unknown under that condition and disappear entirely. In other words, you used an outer join but the result is an inner join. It happens silently, with no error or warning, so the experience of building the two cases side by side in this lab and counting the rows is important.

Another is fan-out. If an order has three line items, the join result has three rows, and if you sum the order amount on top of that, it becomes three times as large. You must always be conscious that a join changes the number of rows.

Steps

Join orders and customers to create a view containing order_id, customer_name, and total_amount, named v_order_customer.
Create a view containing all customers and each customer's number of orders, named v_customer_orders. The columns are id, name, order_count, customers with no orders must be 0, and the number of customers must stay at 400.
Create a view containing the id and name of customers who have never placed an order, named v_never_ordered.
Join order items and products to create a view containing order_id, product_name, quantity, and unit_price, named v_item_detail.
Take the customers whose country is KR, and create a view containing the details of their orders whose status is paid, named v_kr_paid_items. The columns must start with order_id, product_id.
Create a view containing all customers and each customer's number of paid orders, named v_left_paid. The columns are id, paid_count, and the number of customers must stay at 400.
Create a view containing pairs of customers who live in the same city and whose tier is vip, named v_vip_pairs. The columns are a_id, b_id, city, and the same pair must not appear twice.
Create a view containing the orders whose status is one of paid, shipped, or delivered but that have no completed payment, named v_unpaid_orders.

Notes

The habit of counting the rows of a join result first reduces bugs.
Confirm the difference between count(*) and count(컬럼) (the Korean word means "column") yourself in step 2.
Common mistake 1: if you move AND o.status = 'paid' to WHERE in step 6, the number of customers decreases.
Common mistake 2: in step 8, if you only check for the existence of a payment row, you miss orders that have a failed payment.

Attach the customer name to orders

Join orders and customers to create a view containing order_id, customer_name, and total_amount, named v_order_customer.

Write the column that connects the two tables in the ON clause. If you leave out the join condition, rows multiply.

Keep customers who have no orders too

Create a view containing all customers and each customer's number of orders, named v_customer_orders. The columns are id, name, order_count, customers with no orders must be 0, and the number of customers must stay at 400.

Use a join that keeps all of the left table. If you use count(*) when counting, rows with no partner also become 1.

Find customers who have never ordered

Create a view containing the id and name of customers who have never placed an order, named v_never_ordered.

There are several ways to confirm non-existence. NOT EXISTS is safe with NULL.

Connect three tables

Join order items and products to create a view containing order_id, product_name, quantity, and unit_price, named v_item_detail.

You can chain joins several times. Check that you did not leave out the condition in any of the joins.

Apply conditions to a join result

Take the customers whose country is KR, and create a view containing the details of their orders whose status is paid, named v_kr_paid_items. The columns must start with order_id, product_id.

Think about where the condition on the customer table and the condition on the order table should each be attached.

Confirm the difference between the ON clause and the WHERE clause

Create a view containing all customers and each customer's number of paid orders, named v_left_paid. The columns are id, paid_count, and the number of customers must stay at 400.

If you move a condition on the right table to WHERE in an outer join, rows with no partner disappear. Count whether the total number of customers is maintained.

Join the same table with itself

Create a view containing pairs of customers who live in the same city and whose tier is vip, named v_vip_pairs. The columns are a_id, b_id, city, and the same pair must not appear twice.

Give the same table two different aliases. To keep the same pair from appearing twice, put an inequality condition between the ids.

Find orders whose payment is not confirmed

Create a view containing the orders whose status is one of paid, shipped, or delivered but that have no completed payment, named v_unpaid_orders.

You must include both the case where there is no payment row at all and the case where there is one but it failed. You must put the status condition inside the existence condition too.