Introduction
As you become more familiar with SQL, you may find that basic queries are not sufficient for complex data needs. Advanced SQL queries, including subqueries, unions, and window functions, provide powerful tools for sophisticated data analysis and manipulation. This guide explains these advanced SQL concepts with detailed examples.
Subqueries
Subqueries (inner or nested queries) are queries embedded within another query. They are often used for filtering, calculating aggregates, or testing data conditions.
Syntax
SELECT column1, column2
FROM table
WHERE column IN (
SELECT column FROM another_table WHERE condition
);
Example
To find customer names who have placed orders over $200:
SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE amount > 200
);
Result: John, Mary, Steve
Unions
The UNION operator combines results from two or more SELECT statements, removing duplicates. Use UNION ALL to include duplicates.
Syntax
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
Example
SELECT name FROM employees_us
UNION
SELECT name FROM employees_uk;
Result: Alice, Bob, Charlie, David
To allow duplicates:
SELECT name FROM employees_us
UNION ALL
SELECT name FROM employees_uk;
Window Functions
Window functions calculate values across a set of rows related to the current row without collapsing rows into groups.
Common Window Functions
- ROW_NUMBER(): Assigns unique sequential numbers.
- RANK(): Assigns ranks with gaps on ties.
- DENSE_RANK(): Ranks without gaps.
- NTILE(n): Divides rows into
n groups. - LEAD() / LAG(): Accesses next/previous rows.
Syntax
SELECT column1, window_function() OVER (PARTITION BY column ORDER BY column)
FROM table;
Example
Rank sales amounts for each employee:
SELECT sale_id, employee_id, amount,
RANK() OVER (PARTITION BY employee_id ORDER BY amount DESC) AS sales_rank
FROM sales;
Result:
| sale_id | employee_id | amount | sales_rank |
|---|
| 1 | 1 | 500 | 1 |
| 2 | 1 | 300 | 2 |
| 3 | 2 | 700 | 1 |
| 4 | 2 | 400 | 2 |
LEAD() Example
SELECT sale_id, employee_id, amount,
LEAD(amount, 1) OVER (ORDER BY sale_id) AS next_sale
FROM sales;
Conclusion
Advanced SQL techniques like subqueries, unions, and window functions allow you to solve complex data challenges and gain deeper insights. Practice these concepts on real-world datasets to strengthen your SQL skills and become proficient in handling advanced query scenarios.