SQL, the parts I keep looking up

01 Tables, keys and relationships

Every example runs against a trimmed copy of the pgexercises country club.

• A primary key (PK) identifies a row. Postgres enforces it: a second row with memid 1 fails with duplicate key value violates unique constraint "members_pkey", and a NULL key fails too. Names have no such rule, which is why there are two Darren Smiths (1 and 37) and only memid tells them apart.
• A foreign key (FK) holds a primary key value from another table. bookings has two, so it links members to facilities: many members book many facilities, stored as a row per booking.
• recommendedby is a foreign key in its own table: it points at another member, this is referred to as a self-reference (not a circular dependency). To show a recommender’s name you read members twice, once as the member and once as the recommender, and give the two copies different aliases (mems, rec) so SQL knows which firstname you mean. Hover Janice above to see it.

02 How a query actually runs

You write SELECT first; it runs fifth.

select distinct mems.surname, sum(bks.slots) as total56
from cd.members mems1
join cd.bookings bks on bks.memid = mems.memid
where mems.memid <> 02
group by mems.memid3
having sum(bks.slots) > 24
order by total desc, mems.surname7
limit 2;8

• Anything that runs before SELECT can’t see its aliases: WHERE, GROUP BY, HAVING. ORDER BY can.
• Aggregates filter in HAVING, not WHERE. Window functions can’t be filtered in either: wrap the query and filter outside.
• This order decides what a query means, not how fast it runs. Postgres’s planner, the part that decides how to actually fetch the rows, is free to do the work in another order (filtering before joining, say) as long as the result comes out the same.

Further reading: Julia Evans, SQL queries don’t start with SELECT.

03 Joins

ON decides which rows pair up. The join type only decides what happens to rows that find no partner.

select *
from cd.members mems
left join cd.bookings bks
  on bks.memid = mems.memid;

Count rows after every join, the way you would after a pandas merge. Joining from the many side to the one side on its key should never change the row count. If it does, the key isn’t what you think.

Patterns

-- anti-join: members with no bookings
select mems.memid
from cd.members mems
left join cd.bookings bks on bks.memid = mems.memid
where bks.memid is null;

-- semi-join: members who have at least one booking, listed once each
-- (a join would list a member once per booking)
select mems.memid
from cd.members mems
where exists (select 1 from cd.bookings bks where bks.memid = mems.memid);

-- self join: each member with their recommender
select mems.firstname, rec.firstname as recommender
from cd.members mems
left join cd.members rec on rec.memid = mems.recommendedby;

Most joins you write are LEFT or INNER. RIGHT JOIN is a LEFT JOIN with the tables swapped, so it’s rarely written; put the table you want to keep first and use LEFT. CROSS JOIN pairs every row with every row and is only for when you want every combination. A comma between tables, FROM a, b, is also a cross join, which is how a forgotten join condition quietly gives you m × n rows.

04 NULL

SQL has a third truth value. Any comparison with NULL, even NULL = NULL, is neither TRUE nor FALSE but UNKNOWN, and WHERE keeps a row only when its condition is TRUE. Five members have no recommender:

select firstname, surname, recommendedby
from cd.members
where recommendedby = 1 or recommendedby <> 1;

The same thing breaks NOT IN. x not in (1, 4, NULL) is shorthand for x <> 1 and x <> 4 and x <> NULL, and that last part is UNKNOWN for every x:

select firstname, surname
from cd.members
where memid not in (select recommendedby from cd.members);

Outer joins create NULLs, and aggregates skip them. count(*) counts rows, count(col) counts non-NULL values, and sum over nothing but NULLs is NULL:

select mems.memid, mems.firstname, mems.surname,
       sum(bks.slots) as total
from cd.members mems
left join cd.bookings bks on bks.memid = mems.memid
where mems.memid <> 0
group by mems.memid;

05 Filtering: quotes, IN, LIKE

'Tennis' is a string. "Tennis" is a column called Tennis. IN (…) matches exact values from a list; LIKE matches a pattern.

• ~ is a regex match in Postgres, ~* case-insensitive.
• AND binds tighter than OR: a or b and c is a or (b and c). Parenthesise anything that mixes them.

06 Conditional columns with CASE

CASE returns a value, so it goes anywhere a value can: SELECT, WHERE, ORDER BY, inside an aggregate. The first matching WHEN wins; no match and no ELSE gives NULL.

select name, membercost,
       case when membercost = 0 then 'free'
            when membercost < 10 then 'cheap'
            when membercost < 30 then 'mid'
       end as price_band
from cd.facilities;

-- pick which column to calculate with
select bks.bookid,
       case when bks.memid = 0 then bks.slots * facs.guestcost
            else bks.slots * facs.membercost end as cost
from cd.bookings bks
join cd.facilities facs on facs.facid = bks.facid;

-- count conditionally
select count(case when memid = 0 then 1 end) as guest_bookings
from cd.bookings;

07 Aggregation and GROUP BY

select mems.firstname, mems.surname,
       count(*), count(bks.bookid), sum(bks.slots)
from cd.members mems
left join cd.bookings bks on bks.memid = mems.memid
where mems.memid <> 0
group by mems.memid;

• Every selected column is grouped or aggregated, so Postgres knows which single value to show per group. One exception: group by mems.memid and you can still select firstname and surname without grouping by them, because one memid means one member, so each group has only one name.
• Group by the key, not the display name.
• slots are half-hours: hours are sum(slots) / 2.0, and round(x, -1) rounds to the nearest 10.

08 Window functions and ranking

f() over (partition by … order by …) adds a column computed across related rows, and keeps every row.

select memid, starttime, slots,
       sum(slots) over (partition by memid order by starttime) as running
from cd.bookings
where memid <> 0;

Ranking is where ties matter:

select mems.firstname, sum(bks.slots) as total,
       rank()       over (order by sum(bks.slots) desc),
       dense_rank() over (order by sum(bks.slots) desc),
       row_number() over (order by sum(bks.slots) desc)
from cd.members mems
join cd.bookings bks on bks.memid = mems.memid
where mems.memid <> 0
group by mems.memid;

You can rank an aggregate, rank() over (order by sum(slots) desc): GROUP BY has already run. For a repeatable row_number, add a tie-breaker to the ORDER BY.

09 Subqueries and CTEs

select mems.firstname, mems.surname,
       (select rec.firstname
        from cd.members rec
        where rec.memid = mems.recommendedby) as recommender
from cd.members mems;

A CTE is a derived table moved to the top and given a name:

with costed as (
    select bks.bookid,
           case when bks.memid = 0 then bks.slots * facs.guestcost
                else bks.slots * facs.membercost end as cost
    from cd.bookings bks
    join cd.facilities facs on facs.facid = bks.facid
)
select * from costed where cost > 30;

10 Quick reference

Templates

-- filter, conditional column, sort
select col, case when cond then 'a' else 'b' end as label
from t
where col like '%text%'
order by label;

-- join
select a.x, b.y
from a
left join b on b.a_id = a.id;

-- aggregate with a group filter
select key, sum(val) as total
from t
group by key
having sum(val) > 100;

-- rank over an aggregate
select key, sum(val) as total,
       rank() over (order by sum(val) desc) as rnk
from t
group by key;

-- filter on a computed column
select *
from (select *, expr as calc from t) as sub
where calc > 30;

-- whole-day timestamp filter (= '2012-09-21' would match only midnight)
where ts >= '2012-09-21' and ts < '2012-09-22'

Stacking results: UNION

Joins put tables side by side; set operations stack one result on top of another.

select memid from cd.members
union
select memid from cd.bookings
order by 1;

Postgres-only, or Postgres-flavoured

Credits

Every practice question, and the country club schema, comes from pgexercises by Alisdair Owens. The data here is trimmed and adjusted to seed edge cases.