SQL Tutorial for Data Engineers

SELECT specific columns instead of SELECT * to control output and improve performance. In production pipelines, SELECT * is almost always wrong because schema changes break downstream consumers.

WHERE filters rows using comparison operators (=, !=, <, >, <=, >=), BETWEEN for ranges, IN for sets, and LIKE for pattern matching. Combine conditions with AND and OR. Parentheses matter: A OR B AND C groups differently than (A OR B) AND C.

NULL is not a value. It is the absence of a value. WHERE column = NULL does not work. You must use WHERE column IS NULL. This trips up beginners and experienced engineers alike. Three-valued logic (TRUE, FALSE, UNKNOWN) means any comparison involving NULL evaluates to UNKNOWN, which WHERE treats as FALSE.

Aliasing columns with AS makes your output readable. Aliasing tables with short names (FROM orders o) keeps complex queries manageable. You will use table aliases constantly once you start writing JOINs.

ORDER BY executes after SELECT, which means you can sort by column aliases or expressions. You can sort by multiple columns: ORDER BY department ASC, salary DESC sorts by department alphabetically, then by salary descending within each department.

LIMIT and OFFSET together enable pagination, but OFFSET-based pagination performs poorly on large tables because the database still scans all the skipped rows. In production, cursor-based pagination (WHERE id > last_seen_id) is faster.

DISTINCT applies to the entire row, not just the first column. SELECT DISTINCT city, state returns unique city-state pairs, not unique cities. If you want unique values in one column regardless of others, use GROUP BY or a subquery.

Sorting NULL values: in PostgreSQL, NULLs sort last in ASC order and first in DESC order by default. Use NULLS FIRST or NULLS LAST to override. This matters when you are building leaderboards or ranked lists where missing data should appear at the bottom.

COUNT(*) counts all rows including NULLs. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. Knowing which COUNT to use is a common interview question.

GROUP BY groups rows that share the same value in the specified columns. Every column in your SELECT must either appear in GROUP BY or be wrapped in an aggregate function. This rule catches beginners constantly: SELECT name, department, AVG(salary) FROM employees GROUP BY department fails because name is not grouped or aggregated.

HAVING filters groups after aggregation. WHERE filters rows before aggregation. This distinction matters for performance and correctness. WHERE reduces the number of rows the database has to aggregate. HAVING filters the aggregated results. If you can express a condition in WHERE instead of HAVING, do it; it is faster.

Conditional aggregation using CASE WHEN inside aggregate functions is a power technique. SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) counts returned orders without filtering out non-returned ones. This pattern replaces complex self-joins and subqueries in many interview problems.

INNER JOIN returns only rows where the join condition matches in both tables. If a customer has no orders, they do not appear in the result. This is the default JOIN type and the one you will use most often.

LEFT JOIN returns all rows from the left table plus matching rows from the right table. Where there is no match, the right side fills with NULLs. This is how you find 'customers who never ordered' or 'products with no reviews': LEFT JOIN then WHERE right_table.id IS NULL.

Self-joins connect a table to itself. They are essential for hierarchical data (employees and their managers), finding duplicates (rows with the same email but different IDs), and comparing rows within the same table (orders placed within 30 minutes of each other using an inequality join).

JOIN performance depends on the join condition. Joining on indexed columns is fast. Joining on expressions (WHERE LOWER(a.name) = LOWER(b.name)) often forces a full scan. In interviews, mentioning indexing and join order shows you think about production performance, not just correctness.

CROSS JOIN produces the Cartesian product: every row in table A paired with every row in table B. Rarely useful on its own, but combined with LEFT JOIN, it generates 'all possible combinations' that you then fill with actual data. Common in calendar-based reporting where you need a row for every date-product pair even when sales are zero.

Scalar subqueries return a single value. Use them in WHERE clauses: WHERE salary > (SELECT AVG(salary) FROM employees). The database executes the subquery once and uses the result as a constant. Simple and efficient for single-value comparisons.

Correlated subqueries reference the outer query, so they execute once per outer row. WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) compares each employee to their department average. Powerful but potentially slow on large tables because of the per-row execution.

EXISTS and NOT EXISTS check whether a subquery returns any rows. EXISTS stops scanning as soon as it finds one match, making it faster than COUNT(*) > 0 for large tables. NOT EXISTS is the cleanest way to express 'find rows with no matching rows in another table.'

Subqueries in the FROM clause (derived tables) let you pre-aggregate or transform data before joining. SELECT * FROM (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) dept_avg JOIN ... is cleaner than trying to aggregate and join in the same query level.

Basic CTE syntax: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name. You can chain multiple CTEs separated by commas: WITH step1 AS (...), step2 AS (SELECT ... FROM step1) SELECT * FROM step2. Each CTE can reference any CTE defined before it.

CTEs do not inherently improve performance. In PostgreSQL, CTEs used to be optimization fences (materialized by default), but since PostgreSQL 12, the optimizer can inline them. Use CTEs for readability, not performance. If you need to force materialization, add MATERIALIZED.

Recursive CTEs traverse hierarchical data. WITH RECURSIVE org_tree AS (base case UNION ALL recursive case) handles org charts, category trees, and graph traversals. The recursive term references the CTE itself and runs until it produces zero new rows. Always include a termination condition to avoid infinite loops.

The deduplication pattern (WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) AS rn FROM table) SELECT * FROM ranked WHERE rn = 1) combines CTEs with window functions. This pattern appears in nearly every data engineering interview because deduplication is a daily pipeline task.

ROW_NUMBER assigns a unique sequential integer to each row within a partition. RANK assigns the same number to ties but skips the next number. DENSE_RANK assigns the same number to ties without skipping. Interviewers test whether you know which one to use and why. The deduplication pattern uses ROW_NUMBER; ranking with ties uses DENSE_RANK.

LAG and LEAD access previous and next rows without a self-join. LAG(revenue, 1) OVER (ORDER BY month) gets the previous month's revenue. This is how you calculate month-over-month growth, time between events, and change detection. You cannot use LAG in a WHERE clause directly; wrap it in a CTE first.

Frame clauses (ROWS BETWEEN / RANGE BETWEEN) control which rows the window function considers. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW creates a 7-day sliding window. Use ROWS, not RANGE, unless you specifically want to group rows with the same ORDER BY value. Forgetting the frame clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which gives running totals instead of moving averages.

SUM, AVG, COUNT, MIN, and MAX all work as window functions when paired with OVER(). SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) gives a cumulative running total. These aggregate window functions avoid the need for self-joins or correlated subqueries.

NTILE(n) divides rows into n roughly equal groups. NTILE(4) creates quartiles, NTILE(100) creates percentiles. Useful for bucketing users by activity level, creating salary bands, or segmenting customers for A/B tests.

EXPLAIN ANALYZE shows you how PostgreSQL actually executes your query: which indexes it uses, how many rows each step processes, and where time is spent. Sequential Scan means the database reads every row in the table. Index Scan means it uses an index to jump directly to matching rows. If your query uses Sequential Scan on a large table, you probably need an index.

Index your JOIN columns and WHERE filter columns. A JOIN on customer_id without an index on that column forces a full table scan. Create indexes on columns used in equality conditions first, then range conditions. Multi-column indexes follow leftmost prefix rules: an index on (a, b, c) supports queries filtering on (a), (a, b), or (a, b, c), but not (b) or (c) alone.

Avoid functions on indexed columns in WHERE clauses. WHERE LOWER(email) = 'test@example.com' cannot use a regular index on email because the function transforms the value. Use a functional index (CREATE INDEX ON users (LOWER(email))) or store a normalized version of the column.

CTEs, subqueries, and temp tables have different performance characteristics. Subqueries are often inlined by the optimizer. CTEs in PostgreSQL 12+ can also be inlined. Temp tables force materialization, which helps when you reuse a result set multiple times but hurts when the intermediate result is large.