Take your SQL skills beyond the basics with practical examples covering joins, subqueries, CTEs, set operations, date and string functions, conditional aggregation, and filtering with EXISTS or IN.
These patterns are used every day in data pipelines, warehouse transformations, analytics models, and technical interviews. Work through the examples and try the practice questions to build confidence.
Sample datasets used in this chapter
We’ll use two small tables: customers and orders.
customers
| customer_id | customer_name | city |
|---|---|---|
| 1 | Alice | Bangalore |
| 2 | Bob | Mumbai |
| 3 | Charlie | Delhi |
| 4 | Diana | Pune |
orders
| order_id | customer_id | amount | order_date | status |
|---|---|---|---|---|
| 101 | 1 | 1200 | 2026-01-05 | Completed |
| 102 | 1 | 800 | 2026-01-15 | Pending |
| 103 | 2 | 2000 | 2026-02-02 | Completed |
| 104 | 5 | 500 | 2026-02-10 | Cancelled |
Notice that customer 4 has no orders, while order 104 belongs to customer ID 5, which is not present in the customers table. These unmatched records will help us understand joins.
1. INNER, LEFT, RIGHT and FULL JOIN
A JOIN combines rows from two tables using a related column. Joins are essential when building data models from normalized source tables.
1.1 INNER JOIN
An INNER JOIN returns only rows that have a match in both tables.
SELECT
c.customer_id,
c.customer_name,
o.order_id,
o.amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;Example result:
| customer_id | customer_name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 1200 |
| 1 | Alice | 102 | 800 |
| 2 | Bob | 103 | 2000 |
Charlie and Diana are excluded because they have no matching orders. Order 104 is excluded because its customer ID does not exist in customers.
1.2 LEFT JOIN
A LEFT JOIN returns every row from the left table and matching rows from the right table. If no match exists, right-table columns contain NULL.
SELECT
c.customer_id,
c.customer_name,
o.order_id,
o.amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;This includes Alice, Bob, Charlie, and Diana. Charlie and Diana appear with NULL order details.
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;Result: Charlie and Diana.
1.3 RIGHT JOIN
A RIGHT JOIN returns every row from the right table and matching rows from the left table.
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;This includes all orders, including order 104. Since customer ID 5 has no matching customer record, customer_name is NULL for that order.
1.4 FULL OUTER JOIN
A FULL OUTER JOIN returns all matching rows plus unmatched rows from both tables. Missing values on either side are filled with NULL.
SELECT
c.customer_id,
c.customer_name,
o.order_id,
o.customer_id AS order_customer_id,
o.amount
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;The result includes customers without orders (Charlie and Diana) and orders without a matching customer (order 104).
Support for FULL OUTER JOIN varies by database. For example, BigQuery and PostgreSQL support it; some engines require an alternative approach.
JOIN comparison
| Join type | What it returns | Typical use case |
|---|---|---|
| INNER JOIN | Only matching rows | Orders with valid customer records |
| LEFT JOIN | All left rows plus matches | Keep all customers, even without orders |
| RIGHT JOIN | All right rows plus matches | Keep all orders, even without customers |
| FULL OUTER JOIN | All rows from both sides | Reconciliation and identifying unmatched records |
2. Subqueries and Common Table Expressions (CTEs)
2.1 Subqueries
A subquery is a query nested inside another SQL statement. It can be used in a WHERE, FROM, or SELECT clause.
Example: Find orders whose amount is greater than the average order amount.
SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);The inner query calculates the average amount. The outer query returns orders above that average.
Subquery in the FROM clause:
SELECT customer_id, total_amount
FROM (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
) customer_totals
WHERE total_amount > 1000;This first aggregates orders by customer and then filters the aggregated results.
2.2 Common Table Expressions (CTEs)
A CTE is a named temporary result set defined using WITH. It exists for the duration of a single SQL statement and can make complex queries easier to read and maintain.
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount > 1000;The CTE creates a readable logical step called customer_totals, which the main query then uses.
2.3 Multiple CTEs
You can define multiple CTEs and chain them together.
WITH completed_orders AS (
SELECT *
FROM orders
WHERE status = 'Completed'
),
customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM completed_orders
GROUP BY customer_id
)
SELECT *
FROM customer_revenue
WHERE revenue > 1000;This query first filters completed orders, then aggregates revenue by customer, and finally filters customers by revenue.
| Subquery | CTE |
|---|---|
| Nested directly inside another query | Defined using WITH |
| Can be concise for simple logic | Often easier to read when logic has multiple steps |
| May be harder to reuse within a statement | Can be referenced multiple times in the same statement, subject to database rules |
| Performance depends on the optimizer and query | Not automatically faster; materialization and inlining behavior vary by database |
3. UNION vs. UNION ALL
UNION and UNION ALL combine the result sets of two or more SELECT statements. Each SELECT must return the same number of columns with compatible data types in corresponding positions.
3.1 UNION
UNION combines the results and removes duplicate rows.
SELECT city FROM customers
UNION
SELECT city FROM customers_archive;If both tables contain Bangalore, the combined result includes Bangalore only once.
3.2 UNION ALL
UNION ALL combines all rows, including duplicates.
SELECT city FROM customers
UNION ALL
SELECT city FROM customers_archive;If Bangalore appears in both tables, it appears twice in the result.
| UNION | UNION ALL |
|---|---|
| Removes duplicate result rows | Preserves duplicates |
| Usually requires extra work to deduplicate | Usually faster because it does not deduplicate |
| Useful when unique output rows are required | Useful for combining append-only data or partitions |
UNION ALL is often appropriate. Use UNION only when removing duplicates is part of the requirement.Important: SQL set operations match columns by position, not by column name. Use explicit column lists and compatible data types.
4. Date and String Functions
Date and string functions are frequently used to clean source data, build date dimensions, standardize text, and derive reporting attributes. Function names and syntax differ across SQL engines, so check your target database.
4.1 Common date operations
Extract a year or month: In BigQuery, use EXTRACT.
SELECT
order_id,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;Add an interval: In BigQuery:
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS date_plus_7_days
FROM orders;Calculate a date difference: In BigQuery:
SELECT DATE_DIFF(
DATE '2026-02-10',
DATE '2026-02-02',
DAY
) AS days_between;Result: 8 days. The order of the arguments matters: this calculates the first date minus the second date in the specified unit.
Filter a date range:
SELECT *
FROM orders
WHERE order_date >= DATE '2026-01-01'
AND order_date < DATE '2026-02-01';4.2 Common string functions
UPPER and LOWER: Standardize letter casing.
SELECT
UPPER(customer_name) AS name_upper,
LOWER(city) AS city_lower
FROM customers;TRIM: Remove leading and trailing spaces.
SELECT TRIM(' Alice ') AS clean_name;Result: Alice.
LENGTH: Get the length of a string. Exact function names and character-versus-byte behavior vary by engine.
SELECT LENGTH('Bangalore') AS city_length;CONCAT: Combine strings.
SELECT CONCAT(customer_name, ' - ', city) AS customer_location
FROM customers;SUBSTRING: Extract part of a string. This syntax is supported by many databases:
SELECT SUBSTRING('DataEngineering', 1, 4) AS prefix;Result: Data.
REPLACE: Replace occurrences of a substring.
SELECT REPLACE('Data Engineering', 'Engineering', 'Analytics') AS updated_text;Result: Data Analytics.
5. Conditional Aggregation
Conditional aggregation combines aggregate functions with conditions, often using CASE WHEN. It lets you calculate multiple metrics in one grouped query rather than writing separate queries for each metric.
5.1 Count by status for each customer
SELECT
customer_id,
COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS pending_orders,
COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) AS cancelled_orders
FROM orders
GROUP BY customer_id;This produces one row per customer, with separate counts for completed, pending, and cancelled orders.
5.2 Calculate conditional revenue
SELECT
customer_id,
SUM(CASE WHEN status = 'Completed' THEN amount ELSE 0 END) AS completed_revenue,
SUM(CASE WHEN status = 'Pending' THEN amount ELSE 0 END) AS pending_amount
FROM orders
GROUP BY customer_id;Conditional aggregation is useful for building KPI tables, finance reports, sales summaries, and wide analytical datasets.
5.3 Conditional aggregation with a filter
Some databases support aggregate FILTER syntax; support varies. For example, PostgreSQL supports:
SELECT
customer_id,
COUNT(*) FILTER (WHERE status = 'Completed') AS completed_orders,
SUM(amount) FILTER (WHERE status = 'Completed') AS completed_revenue
FROM orders
GROUP BY customer_id;The CASE-based approach is more widely portable across SQL engines.
SUM(CASE WHEN condition THEN amount ELSE 0 END), rows that do not match contribute zero. If all matching amounts are NULL, the result may still depend on the exact CASE expression and data. Decide whether missing amounts should remain NULL or count as zero.6. EXISTS vs. IN
EXISTS and IN are both used to filter rows based on values in another query. They often solve similar problems, but their behavior with NULLs and their query plans can differ.
6.1 IN
IN checks whether a value matches any value in a list or subquery result.
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);This returns customers whose IDs appear in the orders table: Alice and Bob.
6.2 EXISTS
EXISTS checks whether the subquery returns at least one row. It is commonly written as a correlated subquery.
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);For each customer, the database checks whether at least one matching order exists. The query returns Alice and Bob.
6.3 NOT IN vs. NOT EXISTS
A key interview topic is how NULL values affect negated membership tests.
SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);This safely expresses “customers with no matching orders” and returns Charlie and Diana.
By contrast, NOT IN can behave unexpectedly if the subquery returns even one NULL: comparisons may evaluate to UNKNOWN, causing rows to be filtered out. If using NOT IN, ensure the subquery excludes NULL values where appropriate.
| IN | EXISTS |
|---|---|
| Tests whether a value matches any returned value | Tests whether at least one matching row exists |
| Often straightforward for value membership | Often natural for correlated existence checks |
| NULLs can affect IN/NOT IN logic | EXISTS depends on whether rows match the correlation condition |
| Performance depends on the engine and query plan | Performance depends on the engine and query plan |
Final Interview Practice
Try solving each question before expanding the answer.
Question 1: JOINs
Find customers who have never placed an order.
Question 2: Subqueries
Find orders whose amount is greater than the average order amount.
Question 3: CTEs
Calculate total order value by customer, then return customers whose total exceeds 1,000.
Question 4: UNION
What is the key difference between UNION and UNION ALL?
Question 5: Date functions
In BigQuery, return orders placed during January 2026.
Question 6: Conditional aggregation
Count completed orders for each customer.
Question 7: EXISTS
Find customers who have at least one order.
Chapter Summary
By the end of this chapter, you should be able to:
- Choose the right join type and recognize unmatched records and duplicate-key risks.
- Write subqueries and organize multi-step logic with CTEs.
- Explain when to use UNION versus UNION ALL.
- Apply date and string functions to clean and transform data.
- Build multiple metrics with conditional aggregation.
- Use EXISTS and IN correctly, including understanding NULL-related pitfalls.
Comments
Post a Comment