sql.notes
← all levels

Intermediate

joins, grouping, subqueries, the everyday work.

  1. 01INNER JOINReal data is split across tables. Orders don't store the customer's name, they store a customer_id, and the name lives in customers. JOIN is how you stitch them back together.
  2. 02LEFT, RIGHT & FULL OUTER JOININNER JOIN drops anything without a match. But a lot of the time you want to keep those rows. "Show all customers and their orders, including people who never ordered", that's a LEFT JOIN.
  3. 03self joinsA self join is just joining a table to itself. Nothing new syntax wise, but it trips people up because it feels weird at first.
  4. 04ON vs WHERE (join predicates)"Predicate" is just a fancy word for a condition. This topic is about one question: should a condition go in the ON or in the WHERE? For INNER JOIN it doesn't matter. For LEFT JOIN it changes your answer completely.
  5. 05GROUP BY & HAVINGYou saw GROUP BY in the aggregation basics. This goes deeper, especially HAVING, and the difference between WHERE and HAVING which comes up in like every interview.
  6. 06UNION, INTERSECT, EXCEPTJoins put tables side by side (more columns). Set operations stack results on top of each other (more rows). Different tool, different job.
  7. 07subqueriesA subquery is a query inside another query. You use one when you need an answer from the database before you can ask your actual question. Like "products more expensive than average", first you need the average, then you can compare.
  8. 08CTEs (the WITH clause)A CTE (common table expression) is basically a subquery you give a name to and write at the top instead of stuffing it in the middle. Same power, way more readable.
  9. 09string functionsText data is always messy. Extra spaces, random caps, stuff you need to cut or join. These are the functions you'll keep reaching for. Postgres names, mysql differences noted where they matter.
  10. 10date & time functionsAlmost every real question has a time part to it. Revenue this month, users who signed up last week, days since last order. Dates are also where databases differ the most, so I'll stick to postgres and note mysql where it's very different.
  11. 11join fan-out (why your totals are wrong)This is probably the most common bug in real sql, and it doesn't give an error. The query runs, you get a number, the number is wrong. Usually too big.
  12. 12anti-joins (finding what's missing)A normal join finds matches. An anti-join finds rows that don't have a match. "Customers who never ordered", "products never sold", "users who signed up but never logged in". Super common question, and there are three ways to write it, one of which has a nasty bug.
  13. 13numeric functionsMath stuff. Most of it's obvious, but rounding and division have a few traps that cause real bugs, especially with money.