Learn SQL, one sip at a time
Short lessons in plain English. Each one explains a single idea, shows a tiny example, and the rows it gives back. Read one, then try it in today's puzzle.
The basics
Filtering rows
- WHEREWHERE keeps only the rows you want
- Text values go inside single quotesText values go inside single quotes
- Boolean columns hold TRUE or FALSEBoolean columns hold TRUE or FALSE
- ANDAND means both tests must pass
- OROR means either test can pass
- ININ checks a column against a list
- BETWEENBETWEEN covers a range of values
- <><> means 'is not this'
- LIKELIKE matches text against a pattern
- An empty cellAn empty cell is called NULL
- COALESCE replaces a NULL with a fallback valueCOALESCE replaces a NULL with a fallback value
- DatesDates are compared like numbers
Sorting
Counting and summing
- COUNT(*) answers 'how many rows?'COUNT(*) answers 'how many rows?'
- COUNT(DISTINCT ...)COUNT(DISTINCT ...) counts different values
- SUMSUM adds up a whole column
- AVGAVG gives the average of a column
- MIN and MAX find the smallest and largestMIN and MAX find the smallest and largest
- ROUND tidies up a numberROUND tidies up a number
- GROUP BYGROUP BY counts each group separately
- HAVING filters the groupsHAVING filters the groups
Joining tables
- A table aliasA table alias is a short name for a table
- A JOIN connects two tablesA JOIN connects two tables
- You can connect a third table tooYou can connect a third table too
- LEFT JOINLEFT JOIN keeps rows with nothing to connect to
- LEFT JOIN plusLEFT JOIN plus IS NULL finds rows with no match
- CROSS JOIN makes every possible pairCROSS JOIN makes every possible pair
- FULL OUTER JOINFULL OUTER JOIN keeps matched and unmatched rows
Combining results
Going deeper
- A query can hold another query inside itA query can hold another query inside it
- EXISTS asks 'is there at least one?'EXISTS asks 'is there at least one?'
- CASECASE turns values into labels
- A window functionA window function adds a value to each row
- LAG looks at the row beforeLAG looks at the row before
- SUM OVER (ORDER BY ...)SUM OVER (ORDER BY ...) is a running total
- WITH starts a common table expression (CTE)WITH starts a common table expression (CTE)
- ROW_NUMBERROW_NUMBER gives each row a position number
- LEAD looks at the next rowLEAD looks at the next row
- NTILE divides ordered rows into groupsNTILE divides ordered rows into groups