Queries and Joins

7 pages · ~5 min✓ Reviewed

Start here. Next up: Aggregates and GROUP BY.

SQLPage 1

SELECT Query Mental Model

1. What a query is

SELECT picks columns, FROM names the table
A query describes the result you want
The engine chooses how to fetch it
Avoid SELECT * in production code

sql

SELECT name, city
FROM users;

only the columns you need

2. Logical order

FROM / JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

A SELECT alias works in ORDER BY (runs after SELECT)
A SELECT alias fails in WHERE (runs before SELECT)

3. Result is a table

Every SELECT returns rows and columns
So it can feed another query as a subquery

sql

SELECT name
FROM (SELECT name, city FROM users) AS u
WHERE city = 'Pune';

SQLPage 2

Filtering, Sorting and Limiting

1. Filtering Rows

WHERE filters rows before grouping
AND binds tighter than OR: add parentheses
BETWEEN includes both ends

Operator	Meaning
`=` / `<>`	equal / not equal
`IN (1,2,3)`	any value in the list
`BETWEEN 10 AND 20`	10 to 20, inclusive
`LIKE 'A%'`	`%` any chars, `_` one char

sql

SELECT name, city FROM users
WHERE age >= 18 AND city = 'Pune'
  AND (plan = 'pro' OR plan = 'team')
ORDER BY age DESC, name ASC
LIMIT 10 OFFSET 20;

filter, sort on two keys, then page

2. Sorting and Limiting

ORDER BY col DESC, col2 ASC sorts by multiple keys
No ORDER BY: row order is not guaranteed
LIMIT without ORDER BY returns arbitrary rows

Engine	Row limit	ISO standard
PostgreSQL, MySQL, SQLite	`LIMIT 10 OFFSET 20`	no
SQL Server	`TOP 10`	no
Oracle	`OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY`	yes (SQL:2008)

Sort before you page

OFFSET on unsorted rows can skip or repeat rows between pages.

SQLPage 3

NULL Handling

1. What NULL means

NULL = unknown, not 0 or empty string
Any comparison with NULL yields UNKNOWN
col = NULL never matches any row
Use IS NULL / IS NOT NULL

2. Filtering with NULL

WHERE keeps only rows that are TRUE
UNKNOWN rows are dropped
NOT IN (1, NULL) returns no rows

3. Functions and aggregates

COALESCE(col, 'n/a') = first non-NULL argument
NULLIF(a, 0) = NULL when a is 0, so no divide-by-zero

sql

SELECT COALESCE(phone, 'n/a'),
       total / NULLIF(qty, 0)
FROM orders;

COUNT(col) skips NULLs; COUNT(*) counts every row
ORDER BY NULL placement differs by engine: NULLS FIRST / NULLS LAST

Mistake

WHERE col = NULL returns nothing. Write col IS NULL.

SQLPage 4

INNER, LEFT, RIGHT and FULL Joins

1. What a join does

A join combines rows from two tables on a condition
ON names the matching columns; aliases keep them short

sql

SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

2. Join types

Join	Keeps	No match
INNER	matches on both sides	row dropped
LEFT	all left rows	right cols = NULL
RIGHT	all right rows (mirror of LEFT)	left cols = NULL
FULL OUTER	all rows, both sides	NULL on missing side; not in MySQL

3. Anti-join

Rows with no match: LEFT JOIN, then keep the NULL right key

sql

SELECT a.*
FROM a
LEFT JOIN b ON a.id = b.a_id
WHERE b.a_id IS NULL;

Remember

Test IS NULL on a right column that is never NULL otherwise, like the join key.

SQLPage 5

CROSS Joins, Self Joins and Join Mistakes

1. CROSS and self joins

CROSS JOIN = every pairing, m × n rows
No ON clause; use it to generate combinations
Self join: same table twice, with aliases

sql

SELECT e.name, m.name AS manager
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.id

2. Join mistakes

Mistake	Effect	Fix
Right-table filter in `WHERE`	LEFT becomes INNER	Move it to `ON`
Non-unique join key	Fan-out inflates `COUNT`, `SUM`	Join on a unique key
Missing `ON`, comma join	Accidental cross join	Add `ON` / `WHERE`

One-to-many: 1 order × 3 items = 3 rows

LEFT JOIN filter

Filter the right table in ON, not WHERE.

SQLPage 6

SQL Querying Cheat Sheet

1. Query Order

Written: SELECT → FROM → JOIN → WHERE → ORDER BY → LIMIT
Runs: FROM → WHERE → SELECT → ORDER BY → LIMIT
An alias from SELECT works in ORDER BY, not in WHERE

FROMrows
WHEREfilter
SELECTcolumns
ORDER BYsort
LIMITcut

2. NULL

Test with IS NULL / IS NOT NULL; = NULL never matches
COALESCE(x, 'n/a') gives the first non-NULL value
NOT IN with a NULL in the list returns no rows
Prefer NOT EXISTS over NOT IN

3. Join Types

Join	Keeps
`INNER`	matches only
`LEFT`	all left rows
`RIGHT`	all right rows
`FULL`	all rows, both sides
`CROSS`	m × n pairs

4. Pitfalls

SELECT * pulls unneeded columns
LIMIT without ORDER BY returns arbitrary rows
WHERE on the right table of a LEFT JOIN makes it inner
Joining on a non-unique key duplicates rows

SQLPage 7

Check yourself

Table t has ids 1, 2, 3. What does SELECT id FROM t WHERE id NOT IN (1, NULL) return?

None
Row 1 fails id <> 1; rows 2 and 3 give UNKNOWN against NULL, so all are dropped.

Asha has no orders, Ben has a paid one. Who does this return?

Only Ben
Asha's o.status is NULL, so WHERE is UNKNOWN and drops her
Put the status test in ON to keep her

FROM users u LEFT JOIN orders o
  ON o.user_id = u.id
WHERE o.status = 'paid'

Asha has 3 orders. What does SELECT COUNT(*) FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = 1 return?

3, not 1
The join gives one row per order (fan-out)
Use COUNT(DISTINCT u.id) to count users

Why does SELECT price * qty AS total FROM items WHERE total > 100 fail?

WHERE runs before SELECT, so the alias does not exist yet
Repeat the expression in WHERE; ORDER BY total works