Choose the source
FROM identifies the table or result to process.
DATA → QUERY → RESULT
SQL Syntax Map
Understand SQL through how data changes. Switch Snowflake / BigQuery to explore the same concepts in the same order.
SQL at a glance
Core SELECT syntax for querying and analysis. Find a purpose or syntax name and compare results on the same input.
Build the input
Name it / nest it
WITH and a subquery in FROM can express the same intermediate result.
Join horizontally: which rows survive?
Matches / all left / all from both / every combination.
Match condition / filter after joining
In a LEFT JOIN, moving the condition changes which rows survive.
Nested values to rows
Expand elements of a composite value into more rows.
Filter rows
When do you filter?
WHERE before aggregation; HAVING after aggregation; QUALIFY after windows.
Aggregate rows
N rows → 1 value
Choose what to count or which summary to calculate.
Grouping unit / deduplication
GROUP BY defines aggregation units; DISTINCT removes duplicate selected rows.
Analyze without collapsing
Collapse / attach to each row
Aggregate: N rows → one per group. Window: N rows → N rows.
Ranking: what happens to ties?
Unique sequence / ties with gaps / ties without gaps.
Previous row / next row
Attach the preceding and following values in the same order.
Advanced: row position / value range
ROWS uses row positions; RANGE uses ordering values.
Create values
Compare / combine conditions
Comparisons return TRUE, FALSE or NULL; logical operators combine them.
Detect / replace missing values
Equality with NULL is unknown. Test with IS NULL.
General branching / binary shorthand
CASE supports multiple conditions; IF / IFF is compact for two outcomes.
Normal cast / safe cast
Unconvertible value: CAST errors; the safe version returns NULL.
Remove sign / round
ABS returns magnitude; ROUND rounds to the specified precision.
Change case / extract / concatenate
Apply four operations to the same string.
Current / add / difference / truncate
The operation is shared; argument order and unit syntax differ.
Nested value / SQL string
Both may display 7, but their types differ.
Combine sets
Combine vertically: what about duplicates?
One A and B: keep duplicates / remove them / common values / left only.
Advanced: match by column name
Match names even when column positions differ.
Finish the result
Finish the result
Deduplicate → sort → skip initial rows → take up to the limit.