Advanced SQL Queries: Subqueries, Unions, and Window Functions

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:

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.