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 the complexity of your data needs. Advanced SQL queries, including subqueries, unions, and window functions, provide powerful tools for performing sophisticated data analysis and manipulation. These features allow you to create more dynamic queries and gain deeper insights from your data.

In this tutorial, we will explore these advanced SQL concepts with detailed explanations and practical examples. By the end of this guide, you will be equipped to tackle complex SQL challenges and make the most of your database interactions.

Subqueries

Subqueries, also known as inner queries or nested queries, are queries embedded within another SQL query. They are enclosed in parentheses and can be used in various SQL clauses such as SELECT, FROM, WHERE, and HAVING.

Uses of Subqueries

• Filtering results based on the outcome of another query

• Calculating aggregates for use in a larger query

• Testing for existence of data conditions

Syntax

SELECT column1, column2, ...
FROM table
WHERE column IN (SELECT column FROM another_table WHERE condition);

Example

Consider the following tables, orders and customers:

orders

customers

To find the names of customers who have placed orders exceeding $200:

SELECT customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE amount > 200
);

Result:

Unions

The UNION operator is used to combine the results of two or more SELECT queries. It removes duplicate records from the combined result set by default. If you want to include duplicates, you can use UNION ALL.

Syntax

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

Example

Consider two tables, employees_us and employees_uk, that store employee details from different regions:

employees_us

employees_uk

To combine all employee names from both regions:

SELECT name FROM employees_us
UNION
SELECT name FROM employees_uk;

Result:

UNION ALL

To include duplicate names (if any), use UNION ALL:

SELECT name FROM employees_us
UNION ALL
SELECT name FROM employees_uk;

Window Functions

Window functions are advanced SQL features that perform calculations across a set of table rows related to the current row. Unlike aggregate functions, they do not cause rows to become grouped into a single output row, so they retain the original row structure.

Common Window Functions

• ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.

• RANK(): Assigns a rank to each row within a partition, with gaps in the ranking if there are ties.

• DENSE_RANK(): Similar to RANK(), but without gaps in ranking.

• NTILE(n): Divides rows into n buckets and assigns a bucket number to each row.

• LEAD() and LAG(): Access data from subsequent and preceding rows.

Syntax

SELECT column1,
       window_function() OVER (PARTITION BY column ORDER BY column)
FROM table;

Example

Consider a table sales:

sales

To calculate the rank of 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() and LAG()

The LEAD() and LAG() functions are useful for comparing current row data with subsequent or preceding rows.

To view the next sale amount for each row:

SELECT sale_id, employee_id, amount,
       LEAD(amount, 1) OVER (ORDER BY sale_id) AS next_sale
FROM sales;

Result:

Conclusion

Advanced SQL queries enable you to perform complex data operations and analysis. By using subqueries, unions, and window functions, you can enhance your SQL skills and tackle sophisticated data challenges effectively.

These advanced techniques are essential for database professionals and analysts who need to extract valuable insights from data. Practice these concepts with real-world datasets to solidify your understanding and improve your query-writing capabilities.