Advanced SQL Query Techniques

Welcome to the advanced section of our SQL documentation. Here, we delve into powerful techniques that allow you to manipulate and retrieve data in sophisticated ways, enabling complex data analysis and reporting.

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are temporary, named result sets that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, or DELETE). They make complex queries more readable and manageable.

Syntax

WITH cte_name (column1, column2, ...) AS
(
    -- SELECT statement that defines the CTE
    SELECT column1, column2, ...
    FROM your_table
    WHERE some_condition
)
-- Main query that uses the CTE
SELECT *
FROM cte_name
WHERE column1 > 10;
Tip: CTEs can be recursive, allowing you to query hierarchical data.

Window Functions

Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, they do not collapse rows into a single output row. They return a value for each row based on a "window" of related rows.

Types of Window Functions:

Syntax with OVER() clause:

SELECT
    column_name,
    ROW_NUMBER() OVER (ORDER BY some_column) AS row_num,
    SUM(numeric_column) OVER (PARTITION BY category_column ORDER BY date_column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM
    your_table;

Example: Ranking Products by Sales

This query assigns a rank to each product based on its total sales within each category.

SELECT
    product_name,
    category,
    total_sales,
    RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS sales_rank_in_category
FROM
    product_sales;

Subqueries and Correlated Subqueries

Subqueries are queries nested inside another SQL query. Correlated subqueries are a special type where the inner query depends on the outer query for its execution, often running once for each row processed by the outer query.

Correlated Subquery Example: Finding employees with salaries above their department average

SELECT
    e.employee_name,
    e.salary,
    e.department_id
FROM
    employees e
WHERE
    e.salary > (
        SELECT AVG(salary)
        FROM employees
        WHERE department_id = e.department_id
    );
Note: Correlated subqueries can sometimes be performance bottlenecks. Consider alternative approaches like JOINs or CTEs if performance is critical.

Pivot and Unpivot Operations

PIVOT and UNPIVOT operators transform rows into columns and vice versa, which is extremely useful for data aggregation and reporting.

Conceptual Example (Syntax varies by SQL dialect):

PIVOT: Transforming sales data from rows (Date, Product, Sales) to columns (Product, Sales_2023, Sales_2024).

UNPIVOT: Transforming data from wide format (Product, Sales_2023, Sales_2024) back to long format (Product, Year, Sales).

Advanced JOINs

Beyond standard INNER and LEFT JOINs, explore advanced joining techniques: