Query Structure: Advanced

Compare each row against its own group

A correlated subquery references a column from the outer query in its WHERE clause. Unlike a plain subquery that executes once and hands back a fixed result, a correlated subquery re-executes for every outer row using that row's values as input. The practical consequence: you can compute per-row aggregates or existence checks against a related table without collapsing the outer result set, which is something a JOIN cannot do cleanly when aggregation would remove the row-level detail you need.

This query shows each employee with their department's average salary. Notice the correlation: "WHERE e2.department = e.department" - the inner query references e.department from the outer query. For each employee, it calculates their department's average.

Why can't you just use GROUP BY? Because you need each individual employee's row alongside their department average. A regular GROUP BY would collapse all employees in a department into one row. The correlated subquery lets you keep individual employee rows while accessing aggregate department data.

The database starts with the first device and immediately adds it to the result table. Now it needs to fill in the error_count column, so it runs the inner query.

The inner query scans through every single row in device_logs, looking for matching device_ids. When it finds a match, it checks: "Is this error a crash?" If yes, it counts as 1. If no, it counts as 0. After scanning all logs, it sums up those 1s and 0s to get the total crash count for that device.

This process repeats for every device. Each device gets added to the result, then the entire device_logs table gets scanned again for that specific device.

Correlated subqueries are powerful but expensive. Understanding when to use them versus a JOIN or CTE is the key to writing fast, readable queries.

Correlated subqueries are easier to write and read when you need row-by-row calculations in SELECT or WHERE clauses. They clearly express "for each row, calculate this value from related data."

Later, when you learn about JOINs and aggregation (in the Joins and Aggregating lessons), you'll see alternative approaches. For now, correlated subqueries are a powerful tool for row-by-row calculations.

Mistake 2: Using correlated subqueries on massive datasets without indexes. Always index the columns used in the WHERE correlation to avoid full table scans on every outer row.

Finding the latest status for each user by selecting from user_events with a correlated filter. Calculating running totals or ranks within groups. Filtering tables based on complex conditions from related tables using EXISTS.

Row-by-row calculations

Each outer row triggers a fresh inner query execution

Index correlation columns

Without indexes, performance degrades fast on large tables

Runs once per outer row

Keep inner queries simple to avoid compounding cost

SELECT
  product_name
FROM products AS p
WHERE ___ (
  ___
    1
  FROM transactions AS t
  WHERE t.product_id = ___
)

EXISTS stops scanning as soon as it finds the first matching row, making it more efficient than IN for large subquery result sets because it does not need to materialize all matches.

The correlated subquery pattern with EXISTS is the standard way to express semi-join logic in SQL, and modern query optimizers often rewrite IN subqueries into the same execution plan.

NOT EXISTS is the complement and is typically more reliable than NOT IN when the subquery column can contain NULL values, since NOT IN with any NULL in the list returns no rows.

Find rows that exist in one set but not another

EXCEPT subtracts one result set from another, revealing rows that exist in the first query but not the second.

EXCEPT returns rows from the first query that don't appear in the second query. It's incredibly useful for data auditing, finding missing records, or identifying what's changed between two datasets. Think of it as subtraction for SQL; you're removing all matching rows from the first result set.

EXCEPT removes duplicate rows (like UNION), while EXCEPT ALL keeps duplicates (like UNION ALL). Use EXCEPT when you only care which unique records differ. Use EXCEPT ALL when the count of duplicates matters; for example, when comparing inventory snapshots or transaction logs.

The key choice is whether duplicate counts matter. EXCEPT gives you unique unmatched rows; EXCEPT ALL tells you how many times each row is unmatched.

EXCEPT returns rows from the first query that do not appear in the second, showing what is missing or unmatched.

EXCEPT is cleaner when you're comparing entire rows and only need to identify what's missing. It operates on full result sets and clearly expresses "give me rows from A that aren't in B."

For readability and intent, EXCEPT is often the best choice when you simply want to find differences between two datasets without needing to combine columns from both.

EXCEPT shines in data reconciliation, auditing, and finding discrepancies between datasets.

EXCEPT is perfect for data reconciliation tasks. Compare yesterday's customer list with today's to find who unsubscribed. Compare production database with backup to find missing records.

Compare expected test results with actual results to find failures. Any time you need to answer "what's in A but not in B?", EXCEPT is your tool.

Rookie Mistake: Using EXCEPT to "remove duplicates from a table." That's not what EXCEPT does. EXCEPT compares two queries. If you want to deduplicate within one table, use SELECT DISTINCT or GROUP BY instead.

A few technical constraints apply: both queries must select the same number of columns with compatible types, and column names come from the first query. EXCEPT deduplicates its output the same way UNION does; EXCEPT ALL subtracts occurrence counts rather than deduplicating. The operation is also directional: swapping the two sides produces a different result.

EXCEPT is directional: the first query defines the full set and the second query defines what to remove, so swapping the two sides produces a completely different result.

Unlike a NOT IN subquery, EXCEPT automatically handles NULL values correctly by using the same comparison semantics as DISTINCT, which treats two NULLs as equal.

In data quality workflows, EXCEPT is commonly used to find records that are present in a raw ingestion table but absent from the cleaned output, making it easy to detect rows that were filtered out.

-- Find trades booked but missing from risk
-- bookings: T-1001, T-1002, T-1003
-- risk_entries: R-1001, R-1002, R-1004
-- Which bookings have no risk match?

The combination of EXCEPT for finding breaks, INTERSECT for finding matches, and correlated subqueries for row-level comparison represents the full toolkit for multi-system reconciliation in SQL.

Find rows that appear in both result sets

INTERSECT identifies rows that exist in both result sets, like finding the overlap in a Venn diagram.

INTERSECT returns only the rows that appear in both input datasets. Like UNION, it is a set operation, meaning it combines complete result sets from two queries rather than joining individual rows. INTERSECT focuses on overlap rather than combination. Think of it as a Venn diagram; INTERSECT gives you the middle section where both circles overlap.

INTERSECT automatically removes duplicates (like UNION does). If you need to keep duplicates, use INTERSECT ALL. Unlike a JOIN (which you'll learn about in the Joins lessons), INTERSECT operates on entire rows from each query, not just specific columns.

INTERSECT returns only rows that appear in both queries, filtering out anything unique to either side.

Understanding when and how to apply INTERSECT effectively helps you choose the right tool for data comparison tasks.

Use INTERSECT when you want to find exact row matches across all selected columns from two separate queries. It is ideal when you don't need to combine columns from different tables, just identify which rows appear in both result sets.

INTERSECT compares entire rows and returns only distinct matches. If you select multiple columns, INTERSECT ensures all column values match exactly between the two queries.

Both queries must return matching columns with compatible types; column names come from the first query. INTERSECT deduplicates results automatically. Unlike EXCEPT, the order of the two queries does not affect the result. INTERSECT ALL preserves duplicates by returning each matching row the minimum number of times it appears on either side.

Try building an INTERSECT query yourself to find users who belong to both groups.

___
  user_id
FROM orders

___

___
  user_id
FROM transactions

INTERSECT is particularly useful in data reconciliation tasks where you need to confirm that a record exists in two independent systems before treating it as valid.

Because INTERSECT deduplicates its results, it is safe to use even when both source tables contain duplicate rows, which is common in staging layers of a data warehouse.

If duplicate counts matter, INTERSECT ALL preserves them by returning the minimum number of times each row appears across both result sets.

Expand array columns into individual rows

UNNEST expands array columns into separate rows, converting nested data into relational form. An array is a list of values stored in a single cell, like [1, 2, 3] or ["apple", "banana", "cherry"]. This is critical for analyzing complex data structures where multiple values are grouped together.

Why UNNEST exists: Traditional relational tables expect one value per cell. But modern applications often store arrays: [1, 2, 3] or ["tag1", "tag2", "tag3"]. To analyze each array element individually, you need to "unnest" it, which expands the array so each element becomes its own row. Without UNNEST, arrays are opaque strings you can't easily query.

The input table below shows the problem: multiple product IDs squeezed into one cell. You cannot filter, count, or JOIN on values locked inside an array.

Notice the product_ids column contains arrays (lists of values). Each order can have multiple products, but they're all squeezed into one cell. This makes it impossible to count individual SKUs, filter by specific products, or JOIN with a products table to get pricing.

The mechanism: UNNEST takes an array column and creates a temporary table with one row per array element. The comma syntax (FROM table, UNNEST(array)) connects each row to every element in its array. If an order has 3 products, UNNEST generates 3 output rows. The result: one row per array element, making each value queryable individually.

Real-world data is messy. APIs return JSON with arrays. Event logs store multiple tags per event. Analytics tools dump nested structures into your data warehouse. You need UNNEST to: analyze array elements individually, join on values inside arrays, count distinct elements across all arrays, or filter based on array contents.

Example use cases: Analyzing which product_ids appear in user shopping carts (each cart is an array). Counting how many events of each type occurred (event_types stored as arrays).

Flattening user permissions stored as arrays to check individual access rights. Finding which customers bought specific items (order_items as arrays).

Working with arrays in real-world data means encountering NULLs, empty arrays, and non-native array formats like comma-separated strings.

"What happens with empty arrays or NULL?" In most databases: Empty arrays produce zero rows (the original row disappears from the output). NULL arrays also produce zero rows. This can be surprising if you expect all original rows to appear in results.

Be aware that unnesting empty or NULL arrays will cause those rows to be excluded from your results. If you need to preserve rows with empty arrays, you'll learn techniques for this in the Joins lessons.

If your arrays are stored as strings like "101,102,103", you need to split them first using SPLIT(), then UNNEST the resulting array. For example: UNNEST(SPLIT(product_ids, ',')) converts comma-separated values into individual rows.

Which UNNEST approach would you use for a native array column versus a comma-separated string?

Here are the technical details behind how UNNEST operates internally:

SELECT
  order_id,
  t.product_id
FROM orders
CROSS JOIN ___(___) AS t

After unnesting, you can use the expanded rows with any standard SQL clause such as WHERE, GROUP BY, or JOIN, making array columns fully queryable as if they were stored in separate rows.

If an array column contains NULLs or empty arrays, those rows produce zero output rows from UNNEST, which effectively filters them out and can cause a row count decrease that surprises analysts.

UNNEST is especially common in analytics databases like BigQuery and Redshift where repeated fields and array columns are used to denormalize data for storage efficiency.

Generate values without querying a table

SELECT can compute values directly without referencing any table, useful for testing expressions or generating constants.

A SELECT statement does not always require a table. It can be used to compute expressions, constants, or system values directly. This is common in scripts, testing, or when returning a single value (called a "scalar" value) such as timestamps or calculated metrics.

When you're new to SQL, every query you see has FROM. But SELECT is fundamentally about computing and displaying values; the FROM clause just tells SQL where to get row data. Without FROM, you're computing one-time values or testing expressions before embedding them in larger queries.

Without a FROM clause, SQL simply evaluates the expression in SELECT and returns the result.

Even without FROM in the outer query, you can still use subqueries that reference tables. This combines scalar computation (1 +) with table data (COUNT). The outer query has no table, but the inner query does.

Tableless SELECT statements are useful for testing expressions, retrieving system values, and computing constants.

We frequently need to test expressions before embedding them in production pipelines. SELECT without FROM lets you verify date math, test string functions, or check current system values instantly. It's also essential for generating config values, creating dynamic SQL based on calculations, or returning constants in UNION queries where one branch needs a literal value.

"Why doesn't this work everywhere?" Some older systems require a FROM clause even for simple expressions. Using SELECT without FROM is the modern approach and works in most SQL environments.

SELECT
  ___ AS right_now

CURRENT_TIMESTAMP is the most commonly used tableless expression because it provides an audit trail of when a query ran without requiring any additional input.

Many ETL pipelines use SELECT CURRENT_TIMESTAMP to stamp records with a load time, making it easy to identify when each batch of data was processed.

Different databases use different functions (like NOW() in MySQL or GETDATE() in SQL Server), but CURRENT_TIMESTAMP is the ANSI SQL standard and the preferred choice for cross-database compatibility.