Module 12
Merging (joining) tables
Join kinds, composite keys, expanding results and predicting row counts.
You'll be able to
- Choose a join kind
- Merge on several keys
- Spot join explosions
Lessons
- 1.Merge queries: choosing a join kindPick the join kind that returns exactly the rows the question asks for.
- 2.Merge on composite keysJoin tables whose rows are identified by a combination of columns.
- 3.Predict row counts: one-to-many and many-to-many mergesKnow in advance how many rows a merge should produce and check it.
- 4.Expand or aggregate a merge resultChoose between expanding and aggregating nested merge results, and do both correctly.
Take the merging (joining) tables quiz (8 questions)
Exercises
- intermediate~9 minPro
Look up each order's customer city (kite shop)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~9 minPro
Look up each order's customer city (bakery)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~9 minPro
Look up each order's customer city (bike rental)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~8 minPro
Find orders from unknown customers (kite shop)
Use a left anti join to list orders whose customer isn't in the customer master.
- intermediate~8 minPro
Find orders from unknown customers (bakery)
Use a left anti join to list orders whose customer isn't in the customer master.
- intermediate~8 minPro
Find orders from unknown customers (bike rental)
Use a left anti join to list orders whose customer isn't in the customer master.
- advanced~10 minPro
Look up prices by shop and product together (farm shops)
Merge Sales with Prices on both Store and Product, then calculate line revenue.
- advanced~10 minPro
Look up prices by shop and product together (vending)
Merge Sales with Prices on both Store and Product, then calculate line revenue.
- beginner~6 minPro
Keep only enrolments for courses that are still running (college)
Enrolments refer to course codes; the Courses table lists only courses still running. Keep matching enrolments with their title.
- beginner~6 minPro
Keep only enrolments for courses that are still running (academy)
Enrolments refer to course codes; the Courses table lists only courses still running. Keep matching enrolments with their title.
- advanced~14 minPro
Reconcile bank and ledger with a full outer join (charity)
Match bank lines to ledger entries by Ref and flag what's only in the bank, only in the ledger, or in both.
- advanced~14 minPro
Reconcile bank and ledger with a full outer join (studio)
Match bank lines to ledger entries by Ref and flag what's only in the bank, only in the ledger, or in both.
- intermediate~7 minPro
Find products that were never ordered (toy shop)
List catalogue products with no order lines at all, so the buyer can review them.
- intermediate~7 minPro
Find products that were never ordered (stationery)
List catalogue products with no order lines at all, so the buyer can review them.
- advanced~11 minPro
Summarise each customer's orders without multiplying rows (florist)
Join customers to their orders but keep one row per customer, with the number of orders and total quantity.
- advanced~11 minPro
Summarise each customer's orders without multiplying rows (printer)
Join customers to their orders but keep one row per customer, with the number of orders and total quantity.
- intermediate~8 minPro
Expand a one-to-many merge and keep childless parents (agency)
List every task with its project's owner, and keep projects that have no tasks yet as a single row.
- intermediate~8 minPro
Expand a one-to-many merge and keep childless parents (renovation)
List every task with its project's owner, and keep projects that have no tasks yet as a single row.
- intermediate~9 minPro
Clean both keys before merging (deli)
Customer names in Orders and Customers differ in case and stray spaces, so the merge misses matches. Clean the keys and merge.
- intermediate~9 minPro
Clean both keys before merging (cycles)
Customer names in Orders and Customers differ in case and stray spaces, so the merge misses matches. Clean the keys and merge.