TRAINING | DAY 5

Assignment Review & JOINs: Inner, Left, Right, Full, Cross

Combining data from several tables, then analysing the combined result with grouping and filters.

1. The Crux in 60 Seconds

Joins merge tables

Real data lives in several tables. A join pairs rows from two tables on a matching column so you can read them together, like Excel's VLOOKUP.

Pick the join by what you keep

INNER keeps matches only; LEFT keeps every left row; RIGHT every right row; FULL both sides; CROSS pairs every row with every row.

Alias every table

Use o.order_id, r.order_id. Without a prefix a column present in both tables is ambiguous and errors.

Join, then filter and group

The join runs right after FROM and before WHERE. After it, think of the result as one big table for GROUP BY, HAVING and the rest.

2. Foundational Concepts & Comparisons

Excel VLOOKUP

Looks up an order ID in another sheet and fetches its return reason
Shows NA where the order was not returned
Goal: lost sales by return reason, which needs both datasets side by side

SQL join

Matches rows on the join column (order ID) in both tables
Shows NULL where there is no match (in a left join)
Then aggregate as usual: total sales per return reason

Join types at a glance (employee table: 10 rows; department table: 4 rows)

Join	What comes out	Rows in the class demo
INNER	Only rows whose department ID exists on both sides	9 (department 500 has no match)
LEFT (employee left)	All employees; department data NULL when missing	10
RIGHT	All departments; employee data NULL when missing	10 (department 400 has no employee)
FULL OUTER	All matches plus unmatched rows from both sides	11
CROSS	Every row paired with every row; no condition	40 (10 x 4)

The two demo tables

Employee: employee ID, name, department ID, salary, manager ID (10 rows)
Department: department ID, department name (4 rows); for example ID 100 is analytics
Employees use IDs 100, 200, 300 and 500; departments hold 100, 200, 300 and 400, so each side has one unmatched value

Join vs union

A student asked how FULL OUTER JOIN differs from UNION. The instructor: completely different things; he will explain when UNION is taught, to avoid confusing everyone now.

3. Architectural Mechanics: Assignment Review

Q4: subcategories whose average profit exceeds half the maximum profit

SELECT subcategory FROM orders
GROUP BY subcategory
HAVING AVG(profit) > MAX(profit) / 2;

Condition on aggregates, so HAVING. Writing 0.5 * MAX(profit) is the same. Zero rows came back for everyone; an empty result is not wrong.

Q7: top 5 subcategories in the West by quantity

SELECT TOP 5 subcategory,
       SUM(quantity) AS total_quantity
FROM orders WHERE region = 'West'
GROUP BY subcategory
ORDER BY total_quantity DESC;

Run order: FROM, WHERE, GROUP BY, (HAVING), SELECT, ORDER BY, TOP.

The execution-order doubt on Q7

A student asked why total_quantity works in ORDER BY but not in HAVING, since aggregation happens at GROUP BY. The instructor: imagine GROUP BY and HAVING running together while the aggregates are computed; the alias only exists once SELECT has run. What matters is the relative order of the clauses (GROUP BY, then HAVING, then SELECT, then ORDER BY). Do not over-worry about the exact moment the sum is computed.

Q5: total sales by region and ship mode for 2020

Filter orders from 1 Jan 2020 to 31 Dec 2020, then group by region and ship mode
Do not use LIKE on dates or numbers. LIKE is for strings; in many databases it fails on other types and it is slow. Use BETWEEN, =, <, >
A student made LIKE '2020%' work by casting to date, and asked why it fails on a datetime; answer: LIKE suits strings only
Filtering the year with a function (asked as IN) is possible and comes later

NULL status puzzle (student query)

Counting customers by status gave active, inactive and NULL (159 rows)
Adding status <> 'inactive' dropped the NULL rows too
Reason: NULL is neither equal nor not equal to anything
Fix: WHERE status <> 'inactive' OR status IS NULL

Puzzle: students with the same marks in physics and chemistry

Approach 1

SELECT student_id FROM exams
WHERE subject IN ('chemistry','physics')
GROUP BY student_id
HAVING COUNT(*) = 2
   AND COUNT(DISTINCT marks) = 1;

Two rows means both subjects were taken; one distinct mark means they are equal. Student 1 (91 and 91) qualified.

Approach 2

SELECT student_id FROM exams
WHERE subject IN ('chemistry','physics')
GROUP BY student_id, marks
HAVING COUNT(*) = 2;

Grouping on student and marks: only equal marks form a group of two rows.

Filter first: put the subject filter in WHERE straight away. It shrinks the data and, here, also keeps students with other subjects (maths, biology) from qualifying wrongly
A student's variant HAVING COUNT(*) > COUNT(DISTINCT marks) works for two subjects but fails for a "same marks in three subjects" version. Write queries that generalise

GROUP BY columns that are not in SELECT

Allowed: SELECT student_id ... GROUP BY student_id, marks works, and student IDs may then repeat. The reverse (SELECT a column that is neither grouped nor aggregated) errors, because the database would not know which value to show.

3. Mechanics (continued): INNER JOIN and LEFT JOIN

Setting up the returns table

Returns: order ID and return reason; 296 returned orders; three reasons (bad quality, wrong items, others)
Copied from another database with a SELECT * INTO style statement (INTO explained later)
Snag: that table was created before the case-sensitivity script, so joining gave "invalid column" errors (case-sensitive vs case-insensitive tables)
Fix: rename the old table to a backup, create a new returns table, then INSERT INTO returns SELECT * FROM backup
Red underlines after creating a table: Ctrl+Shift+R (Edit, IntelliSense, Refresh Local Cache)

Syntax

SELECT o.order_id, o.order_date,
       r.return_reason
FROM orders o
INNER JOIN returns r
  ON o.order_id = r.order_id;

o and r are table aliases; ON states the matching columns
Plain JOIN means INNER JOIN; always write the type
The join columns may have different names; they must have compatible data types

How the matching works (mini example)

Left roworder 1

→

Compare on ON1 = 1? yes

→

MatchRow joined; reason shown

Orders table has orders 1 and 2; returns holds 1 (bad item) and 3. Order 1 joins; order 2 and return 3 have no partner, so INNER JOIN drops them. The result had about 800 rows because each order ID can have several product rows, yet only 296 distinct order IDs (checkable with COUNT(DISTINCT o.order_id)).

Aliases and ambiguity

order_id exists in both tables, so unprefixed use errors: "ambiguous column name"
Prefix with the table name (orders.order_id) or an alias; aliases keep long queries readable
o.* gives all columns of orders; o.*, r.return_reason works unprefixed too since return_reason lives only in returns
Always alias, even when not ambiguous, so readers see where each column comes from

No key relationship needed

Joining on columns that are not declared as primary or foreign keys is allowed
Analytical databases (Redshift, SAP HANA) do not enforce such relationships; in practice you usually join on keys
Duplicates on both sides (many-to-many) are postponed to later

LEFT JOIN (like VLOOKUP)

FROM orders o
LEFT JOIN returns r
  ON o.order_id = r.order_id

All rows of the left table (the one written before LEFT JOIN): 9,994 rows. Unmatched rows get NULL in r.return_reason and r.order_id.

Orders never returned

... LEFT JOIN returns r
  ON o.order_id = r.order_id
WHERE r.order_id IS NULL

Very common interview question. Any right-table column works for the NULL test. Result: 9,994 minus the 800 returned rows = 9,194.

Analysis after the join

SELECT r.return_reason, SUM(o.sales) AS total_sales ... GROUP BY r.return_reason. With LEFT JOIN a NULL group appears (orders never returned), with INNER JOIN only the returned orders remain. After the join, ORDER BY, TOP, HAVING and the rest work exactly as before.

3. Mechanics (continued): CROSS, RIGHT, FULL and Multiple Joins

CROSS JOIN

SELECT * FROM employee, department;
-- or
... INNER JOIN department ON 1 = 1

No join condition: every row pairs with every row; rows = rows in A times rows in B (2 x 3 = 6 in the mini example; 10 x 4 = 40 in the demo)
ON 1 = 1 is an always-true workaround; uses are shown later

RIGHT and FULL OUTER

RIGHT: everything from the table written after RIGHT JOIN; mirror image of LEFT. It equals a LEFT JOIN with the tables swapped
The instructor never used RIGHT JOIN in 12 years; know it for interviews, use LEFT
FULL OUTER: all matches plus unmatched rows from each side (nulls fill the missing side)
Only the join keyword changes between versions of the query

Employee and department results (join on department ID)

Join	Rows	Notable rows
INNER	9	Employee with department 500 is missing
LEFT (employee first)	10	Department 500 employee present; department name NULL
RIGHT	10	Department 400 present with NULL employee data; department 500 employee not shown
FULL OUTER	11	Both the department 500 employee and department 400

The two department-ID columns have different names; that is fine, only the values must match. Group by after joining gives insights such as salary by department name.

Three or more tables

... INNER JOIN people p
  ON o.region = p.region

The people table (region and regional manager: West, East, Central, South) adds the manager to each returned order
The ON condition may use any table already joined; operand order is irrelevant (p.region = o.region is identical)
Joins run top to bottom: first join gives a result, the next join works on that result
No limit; the instructor sees 8 to 10 at most, rarely above 10

Chain trap

Imagine each intermediate result, or run it, before adding the next join
After a LEFT JOIN, unreturned orders carry a NULL r.order_id; a later INNER JOIN on that NULL column drops them
The intermediate result is virtual, not a created table; giving it an alias and joining it is a subquery topic
Right join scenario raised by a student (needing all rows of the first and last tables) to be explored; he suggests the middle table first, then LEFT JOINs

Test runs and what they proved

#	Run	Result
1	Join on `order_id` with no alias prefix	Error: ambiguous column
2	INNER JOIN orders and returns	Only returned orders, with reasons
3	Same with LEFT JOIN	All 9,994 rows; NULLs for unreturned orders
4	Add `WHERE r.order_id IS NULL`	Never-returned orders only
5	Join `order_id` (text) with sales (float)	Error converting varchar to float
6	Join `order_id` with `order_date`	Error: conversion failed from character string to date or time

The database converts both join columns to a common type internally; if the text cannot convert, the join fails. Joining an integer with a date would never match anyway.

5. Best Practices & Golden Rules

Pitfall: LIKE on dates or numbers

Use range or comparison operators; LIKE is for strings and is slow elsewhere.

Pitfall: unprefixed shared column

A column present in both tables gives "ambiguous column name".

Rule: filter early

Put WHERE filters first (for example the subject list in the exam puzzle); less data speeds up everything after.

Rule: alias every table and column source

Even where not required, so the query explains itself.

Rule: prefer LEFT over RIGHT

Put the driving table first and LEFT JOIN the rest; swap tables instead of using RIGHT.

Rule: test doubts with tiny tables

Create a table, insert two or three rows, run it. No keys or constraints needed for R&D.

Trainer Q&A recap

Is join without keys good practice? Allowed; relationships are not forced in analytical databases.
What if both tables have duplicates? Postponed to a later class.
Plain JOIN? It is an INNER JOIN.
Do join column names have to match? No, only compatible types.
Can COUNT take two columns? No, one argument only. COUNT(DISTINCT a, b) fails; count the rows of a SELECT DISTINCT a, b subquery instead (subqueries later).
Is the joined result a table? Not created; virtual, can be imagined as one.
Next steps: assignment, returns data file and employee/department scripts to be shared; doubt session planned for the weekend.

6. Numbers & Metrics to Remember

296Returned orders in the returns table

9Rows in the employee-department inner join

11Rows in the full outer join

40Rows in the cross join (10 x 4)

7. Mini Glossary

Join:: combining rows of two tables based on a condition.
ON:: keyword introducing the join condition.
Left table:: the table written before LEFT JOIN.
Ambiguous column:: a column name present in several joined tables.

Alias:: short name for a table or column (o, r).
Cross join:: every row of A paired with every row of B.
Full outer join:: matches plus unmatched rows from both tables.
VLOOKUP:: Excel function whose job joins do in SQL.

8. Quick Self-Test

What does INNER JOIN return, and what happens to unmatched rows on each side?
How does LEFT JOIN resemble VLOOKUP, and what replaces VLOOKUP's NA?
Scenario: you need orders that were never returned. Write the join and filter.
Why does SELECT order_id FROM orders o JOIN returns r ON ... error, and how do you fix it?
Employee has 10 rows, department has 4. How many rows for CROSS, INNER (9 matches) and FULL OUTER (one unmatched row on each side)?
Scenario: after a LEFT JOIN you INNER JOIN a third table on r.order_id. What happens to unreturned orders and why?
Scenario: a student's status <> 'inactive' hides customers with no status. Why, and what is the fix?
Why should the subject filter sit in WHERE in the same-marks puzzle, and why does COUNT(*) > COUNT(DISTINCT marks) not generalise?