TheHauData Mentoring Program · Track 1 · Lesson 3

Combining Data in SQL

One-page recap of the slides, then every classwork and homework solved step by step with a table map. Data: AdventureWorksLight and CRM Sales.

Combine sideways with JOIN (more columns). Stack downwards with UNION (more rows). LEFT JOIN and INNER JOIN cover about 80% of real work.

Why Combine data at all?

Warehouses keep data in many small related tables. A single 5-billion-row table with every column repeated costs millions of dollars a month in storage.

So we store once, and combine only when we report or analyse.

1 BIG table5B rows × 25 cols vs fact

Two directions JOIN vs UNION

UNION needs the same structure: same number of columns, same order, compatible data types. JOIN needs a matching key.

Data model Fact, dimension, keys

  • Fact table: transactions or events. Large, updated all the time (Sales_orders, Sales_orders_detail).
  • Dimension table: descriptive context. Small, rarely changes (Dim_Product, Dim_Territory).
  • Primary key (PK): unique, never NULL. Foreign key (FK): points to a PK in another table.
1–1 one row ↔ one row1–N one customer ↔ many ordersN–N orders ↔ reasons (via a bridge table)

Process Four steps before every JOIN

  1. Expected output first. Pick the starting (LEFT) table: the one whose rows you must keep, usually the fact table or the more detailed one.
  2. List the extra info you need and which table holds it.
  3. Find the keys that connect the tables. Check their data types match.
  4. Run, then validate: row count, NULLs, duplicates.

JOIN types What each one keeps

Stack rows UNION vs UNION ALL

UNION removes duplicate rows. UNION ALL keeps everything and is faster.

In class: orders of the 2011 and 2012 top-10 customers gave 160 rows with UNION and 180 with UNION ALL. Two customers are in both lists.

Use for combining final results, e.g. a "Shortest" and a "Longest" list with a label column.

Watch out Mistakes seen in this lesson's practice

  • Key types differ. CreditCardID and ProductSubcategoryID are stored as different types in the course database, so CAST(... AS VARCHAR) one side before joining.
  • LEFT JOIN + WHERE on the right table removes the unmatched rows, so it behaves like INNER JOIN.
  • Keys that look equal but are not. CRM has GTXPro in the pipeline and GTX Pro in products: 729 won 2017 deals silently drop out of HW Q1.
  • One-to-many multiplies rows. Orders joined to reasons repeat an order per reason. Use DISTINCT or aggregate, then check the row count.

Interview favourites Two "same result" questions

  • INNER = LEFT? Yes: LEFT JOIN ... WHERE right.key IS NOT NULL returns exactly the INNER JOIN rows.
  • FULL = LEFT + RIGHT? Yes: LEFT JOIN, then UNION the RIGHT JOIN. UNION removes the matched rows that appear in both.

Try both in the JOIN lab.

Source: Lesson 3.pdf (Quoc-Hau Le, TheHauData). Row counts verified by running the course SQL on the CSV datasets.

Map Tables used in Lesson 3

Orange = fact table (many rows). Blue = dimension. Arrow label = join key. ⚠ = stored with different data types in the course database, so CAST one side.

Source: Class Practice_L03.sql and HW_Ref_solution_L03.sql. SQL shown is T-SQL as in the answer key. Result tables come from running equivalent queries on the CSV files.

Lab See what each JOIN keeps

Illustrative mini tables. Pick a join and watch which rows survive. Order 105 has a NULL territory: NULL never equals anything, so it never matches.

Left: Orders (t1)

Right: Territory (t2)

Result

matched pairkept without a match (NULLs filled in)row appears in result

    
  


  

    
Lab UNION vs UNION ALL

    

    

      
Query A: top 2011

      
Query B: top 2012

      
Result