SQL Fundamentals and Query Basics

-- UNION: deduplicates the combined result set
SELECT city FROM current_customers
UNION
SELECT city FROM past_customers;

-- UNION ALL: keeps every row, including duplicates
SELECT city FROM current_customers
UNION ALL
SELECT city FROM past_customers;

Requirements

Every SELECT in the union must produce the same number of columns, in the same order, with compatible data types. Column names in the output come from the first SELECT.

Performance

UNION has to identify and remove duplicates, which typically means sorting the combined result or building a hash set — an extra pass that costs memory and CPU proportional to the result size. UNION ALL just concatenates the result sets with no extra work.

Rule of thumb: default to UNION ALL unless you specifically need deduplication. A common mistake is reflexively using UNION "to be safe" on two queries that can never produce overlapping rows (e.g., querying two mutually exclusive partitions), paying for a sort that can never actually remove anything.

Ordering the final result

ORDER BY can only appear once, at the end, and applies to the combined result:

SELECT city, 'current' AS source FROM current_customers
UNION ALL
SELECT city, 'past' AS source FROM past_customers
ORDER BY city;

INTERSECT and EXCEPT are the other two set operators alongside UNION, each with the same column-count/type requirements.

-- INTERSECT: rows present in BOTH result sets
SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;
-- customers who ordered in both years

-- EXCEPT (Oracle: MINUS): rows in the first set but NOT the second
SELECT customer_id FROM orders_2023
EXCEPT
SELECT customer_id FROM orders_2024;
-- customers who ordered in 2023 but churned before 2024

Equivalent using joins/EXISTS

These set operators are often more readable than the join-based equivalent, but the optimizer typically rewrites them to a semi-join or anti-join internally anyway:

-- Equivalent to the INTERSECT above
SELECT DISTINCT o23.customer_id
FROM orders_2023 o23
WHERE EXISTS (SELECT 1 FROM orders_2024 o24 WHERE o24.customer_id = o23.customer_id);

-- Equivalent to the EXCEPT above
SELECT DISTINCT o23.customer_id
FROM orders_2023 o23
WHERE NOT EXISTS (SELECT 1 FROM orders_2024 o24 WHERE o24.customer_id = o23.customer_id);

Support across engines

• PostgreSQL, SQL Server, SQLite: INTERSECT and EXCEPT
• Oracle: INTERSECT and MINUS (not EXCEPT)
• MySQL: added INTERSECT and EXCEPT in 8.0.31+; earlier versions require rewriting as JOIN/EXISTS

Both operators deduplicate by default, and (like UNION) most engines also support an ALL variant (INTERSECT ALL, EXCEPT ALL) that preserves duplicate counts using multiset semantics, though these are used far less often.

CREATE TABLE example (
    country_code CHAR(2),      -- always exactly 2 chars, e.g. 'US', 'GB'
    username     VARCHAR(50),  -- up to 50 chars, stores only what's used
    biography    TEXT          -- arbitrarily long
);

CHAR(n) — fixed length

• Always consumes storage for exactly n characters; shorter values are right-padded with spaces (trailing spaces are typically stripped on read, depending on engine).
• Best for values that are genuinely always the same length: fixed codes like ISO country codes, US state abbreviations, MD5 hex hashes.
• Slightly faster comparisons in some engines because rows are uniformly sized, simplifying row offset math — but this rarely matters in practice compared to correct data modeling.

VARCHAR(n) — variable length, bounded

• Stores only the actual bytes used, plus 1–2 bytes of length prefix.
• The (n) is a maximum, enforced at insert/update time — it's a constraint, not pre-allocated storage.
• The right default for most string columns: names, emails, addresses, titles.

TEXT / unbounded — variable length, no practical cap

• PostgreSQL: TEXT has no length limit and, importantly, has no performance penalty vs VARCHAR(n) — internally they use the same storage mechanism (TOAST for large values), so PostgreSQL docs actually recommend TEXT with a CHECK constraint over VARCHAR(n) for flexibility.
• MySQL/SQL Server: TEXT (or NVARCHAR(MAX)) historically had different storage (often off-page/BLOB-like) and couldn't always be indexed the same way as VARCHAR, or required a prefix index. This has narrowed in modern versions but still varies — check your engine's docs before assuming TEXT behaves identically to VARCHAR(MAX).

Pick VARCHAR(n) with a sensible max when there's a genuine business-rule length limit (e.g., a 100-character product name) so the constraint documents intent and catches bad data early. Use TEXT for genuinely unbounded content (article bodies, JSON blobs, logs). Avoid CHAR(n) unless the value truly always has that exact length — using it for a "mostly short" string wastes storage and requires careful trimming on comparison.

Basic syntax

SELECT
    order_id,
    CASE
        WHEN total > 1000 THEN 'large'
        WHEN total > 100  THEN 'medium'
        ELSE 'small'
    END AS order_size
FROM orders;

Conditions are checked top to bottom; the first matching WHEN wins. ELSE is optional — if omitted and no branch matches, the result is NULL.

There's also a simpler "simple CASE" form for equality checks against one expression:

SELECT
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_label
FROM users;

Conditional aggregation — the most powerful use case

Combining CASE with an aggregate lets you pivot conditional counts/sums into columns without a separate query per condition:

SELECT
    department,
    COUNT(*) AS total_employees,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count,
    SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners,
    AVG(CASE WHEN hire_date >= '2023-01-01' THEN salary END) AS avg_new_hire_salary
FROM employees
GROUP BY department;

Note the last example: AVG(CASE WHEN ... THEN salary END) (no ELSE) — rows that don't match the condition contribute NULL, and aggregates like AVG/SUM/COUNT(column) ignore NULLs, so this correctly computes the average only over matching rows without needing a separate WHERE-filtered query.

In ORDER BY — custom sort order

SELECT * FROM tickets
ORDER BY
    CASE priority
        WHEN 'critical' THEN 1
        WHEN 'high'     THEN 2
        WHEN 'medium'   THEN 3
        ELSE 4
    END;

This is the standard trick for sorting by a business-meaningful order that doesn't match alphabetical or numeric order of the raw column.

In WHERE / GROUP BY

CASE can also drive filtering or bucketing logic:

SELECT
    CASE WHEN age < 18 THEN 'minor' WHEN age < 65 THEN 'adult' ELSE 'senior' END AS age_bucket,
    COUNT(*)
FROM people
GROUP BY 1;  -- most engines allow grouping by output column position

These three terms describe overlapping ways of nesting one query inside another, distinguished mainly by where they appear and how reusable they are.

Subquery — the general term

Any SELECT nested inside another SQL statement:

-- In WHERE (scalar/list subquery)
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE region = 'EU');

-- In SELECT (scalar subquery, must return exactly one value)
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

Derived table — a subquery used as a table

When a subquery appears in FROM and is given an alias, it's specifically called a derived table (or "inline view"):

SELECT dept_avg.department, dept_avg.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg          -- <-- the alias is mandatory in most engines
WHERE dept_avg.avg_salary > 80000;

Derived tables must be aliased, are scoped only to that one query, and can't be referenced more than once without repeating the whole subquery.

CTE — a named, top-level, (optionally) reusable query

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

Advantages over a derived table:

• Readability — complex queries read top-to-bottom instead of nesting inward.
• Reusability within one statement — the same CTE can be joined against multiple times without repeating its definition.
• Recursion — WITH RECURSIVE lets a CTE reference itself, which is impossible with a plain derived table (used for hierarchies/graphs — see the recursive CTE question).

Materialization caveat

Historically, PostgreSQL always materialized (fully computed and stored) CTE results before using them, which could hurt performance versus an equivalent derived table the optimizer could inline and push predicates into. As of PostgreSQL 12, CTEs are inlined by default (like a derived table) unless marked MATERIALIZED, referenced multiple times, or recursive. Always check your engine's current CTE optimization behavior rather than assuming — it's one of the more version-sensitive areas of SQL.

Non-correlated subquery

Self-contained — it doesn't reference anything from the outer query, so it can be evaluated exactly once:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner SELECT AVG(salary) FROM employees computes one number, once, regardless of how many outer rows there are.

Correlated subquery

References a column from the outer query, so its result depends on which outer row is currently being evaluated:

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e.department   -- <-- correlated: references outer e.department
);

Here, the inner query's result (average salary) is different for every department, so conceptually it must be recomputed for each outer row.

Common use case: "latest/top row per group"

SELECT o.*
FROM orders o
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id   -- correlated
);

This finds each customer's most recent order — a pattern that's hard to express without either a correlated subquery, a window function (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)), or a GROUP BY + join.

Performance implications

A naive mental model — "the subquery runs once per outer row" — describes correctness, not necessarily execution. Modern optimizers frequently rewrite correlated subqueries into semi-joins or anti-joins (especially ones using EXISTS/IN/NOT EXISTS), avoiding literal row-by-row re-execution. But not all correlated subqueries can be rewritten this way, and on an engine/query shape where the optimizer can't flatten it, you do get an actual nested-loop-style re-execution per outer row — which is O(n×m) and can be dramatically slower than an equivalent join or window function on large tables.

Practical guidance: correlated subqueries are fine and often the clearest way to express "compare each row to something computed from its own group." But when you hit a performance problem with one on a large table, check the execution plan first — a rewrite to a JOIN with GROUP BY, or a window function, is often available and can be an order of magnitude faster.

-- DISTINCT: unique combinations of the selected columns
SELECT DISTINCT department FROM employees;

-- GROUP BY: same result here, but built for aggregation
SELECT department FROM employees GROUP BY department;

-- GROUP BY's real purpose: per-group aggregates
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department;

Where they diverge

DISTINCT applies to every selected column together — it can't aggregate, and it can't return a value for one row that summarizes a group of others:

-- Returns one row per DISTINCT (department, job_title) combination
SELECT DISTINCT department, job_title FROM employees;

GROUP BY lets you select the grouping column(s) plus arbitrary aggregate expressions over each group — something DISTINCT simply cannot do:

SELECT department, job_title, COUNT(*) AS headcount, MAX(salary) AS top_salary
FROM employees
GROUP BY department, job_title;

Attempting to SELECT a non-aggregated, non-grouped column alongside GROUP BY is a functional-dependency violation that most engines reject (ONLY_FULL_GROUP_BY in MySQL) or, in older/lenient MySQL modes, silently returns an arbitrary value from the group — a frequent source of subtly wrong reports.

Performance

When there's no aggregate function involved, SELECT DISTINCT col FROM t and SELECT col FROM t GROUP BY col typically produce identical execution plans — both need some form of sort or hash-based deduplication, and query optimizers usually recognize the equivalence. Don't assume one is inherently faster than the other without checking EXPLAIN on your specific engine and data.