Top 15 Advanced SQL Tricks

1. Window Functions for Complex Calculations

Window functions perform calculations across a set of table rows that are related to the current row. They are invaluable for tasks like ranking, calculating running totals, moving averages, and more, without collapsing rows like GROUP BY.

SELECT
    order_id,
    order_date,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS rank_by_amount,
    ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM order_date) ORDER BY amount DESC) AS row_num_yearly,
    SUM(amount) OVER (PARTITION BY EXTRACT(YEAR FROM order_date) ORDER BY order_date) AS running_total_yearly
FROM
    orders;

Performing calculations across partitions of data.

2. Common Table Expressions (CTEs) for Readability and Recursion

CTEs (defined using the WITH clause) are temporary, named result sets that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. They improve query readability by breaking down complex logic into smaller, manageable parts and are essential for recursive queries (hierarchical data).

-- Non-recursive CTE for readability
WITH HighValueOrders AS (
    SELECT order_id, customer_id, amount
    FROM orders
    WHERE amount > 100
)
SELECT c.customer_id, COUNT(hvo.order_id) AS high_value_order_count
FROM customers c
JOIN HighValueOrders hvo ON c.customer_id = hvo.customer_id
GROUP BY c.customer_id;

-- Recursive CTE for hierarchical data (e.g., employee hierarchy)
WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, employee_name, 0 AS level
    FROM employees
    WHERE manager_id IS NULL -- Top-level manager

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT level, employee_name FROM EmployeeHierarchy ORDER BY level;

Structuring complex queries and handling hierarchical data.

3. Pivot and Unpivot for Data Transformation

Pivoting transforms rows into columns, while unpivoting does the opposite. These operations are useful for summarizing data in different formats for reporting or analysis.

-- Pivot (example syntax might vary slightly by database)
SELECT *
FROM (
    SELECT category, product_name, sales
    FROM product_sales
) AS source_table
PIVOT (
    SUM(sales) FOR category IN ('Electronics', 'Clothing', 'Books')
) AS pivot_table;

-- Unpivot (example syntax might vary slightly by database)
SELECT product_name, category, sales
FROM pivot_table
UNPIVOT (
    sales FOR category IN (Electronics, Clothing, Books)
) AS unpivot_table;

Reshaping data for better analysis and presentation.

4. Using EXISTS and NOT EXISTS for Efficient Subquery Checks

EXISTS and NOT EXISTS are used to check for the existence of rows in a subquery. They are often more performant than using IN or NOT IN, especially with large subqueries, as the database can stop processing the subquery as soon as a matching row is found.

-- Find customers who have placed orders
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- Find customers who have not placed any orders
SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Efficiently checking for the presence or absence of data.

5. Advanced Filtering with QUALIFY (Some Databases)

The QUALIFY clause (available in some databases like Snowflake and Teradata) allows filtering the results of window functions directly, without needing a subquery or CTE.

SELECT
    order_id,
    order_date,
    amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS customer_rank
FROM
    orders
QUALIFY customer_rank 

Filtering based on the output of window functions.

6. Working with JSON and XML Data (Database Dependent)

Modern databases often have built-in functions to query and manipulate JSON and XML data directly within SQL. This is crucial for dealing with semi-structured data.

-- PostgreSQL JSON functions
SELECT
    data ->> 'name' AS product_name,
    (data -> 'details' ->> 'price')::numeric AS price
FROM
    products
WHERE
    (data -> 'details' ->> 'color') = 'red';

-- MySQL JSON functions
SELECT
    JSON_EXTRACT(data, '$.name') AS product_name,
    JSON_EXTRACT(data, '$.details.price') AS price
FROM
    products
WHERE
    JSON_EXTRACT(data, '$.details.color') = 'red';

Querying and manipulating semi-structured data within SQL.

7. Full-Text Search Capabilities (Database Dependent)

Many databases offer built-in or extension-based full-text search functionality, allowing you to perform sophisticated text searches using keywords, phrases, and boolean operators.

Performing advanced text searches.

8. Using GENERATE_SERIES for Creating Sequences

The GENERATE_SERIES function (or similar in other databases) allows you to create a series of numbers or dates, which can be useful for generating reports, filling in missing data, or creating test data.

-- PostgreSQL
SELECT generate_series(1, 10);

-- PostgreSQL generating dates
SELECT generate_series(
    '2024-01-01'::date,
    '2024-01-10'::date,
    '1 day'::interval
)::date;

Generating sequences of numbers or dates.

9. Understanding Different JOIN Types and Their Performance Implications

Beyond INNER JOIN and LEFT JOIN, understanding RIGHT JOIN, FULL OUTER JOIN, and various types of LATERAL JOIN (or APPLY in some databases) can unlock more complex data retrieval scenarios. Being aware of how the database executes different join types is crucial for optimization.

-- Lateral Join (PostgreSQL) - Join each row with the result of a set-returning function
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT order_id, order_date
    FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
    LIMIT 1
) AS o ON TRUE;

Leveraging advanced join types for complex relationships.

10. Using Subqueries Effectively (Correlated vs. Non-Correlated)

Understanding the difference between correlated (dependent on the outer query) and non-correlated (independent) subqueries is important for both logic and performance. Non-correlated subqueries are often executed only once, while correlated subqueries are executed for each row of the outer query.

-- Non-correlated subquery (executed once)
SELECT AVG(amount) FROM orders;

SELECT *
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Correlated subquery (executed for each customer)
SELECT c.customer_id, c.customer_name,
       (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS latest_order_date
FROM customers c;

Optimizing subquery usage based on dependency.

11. Optimizing Queries with EXPLAIN/ANALYZE

Most database systems provide commands like EXPLAIN (or EXPLAIN PLAN) and ANALYZE to show the query execution plan. Understanding this plan is crucial for identifying potential performance bottlenecks (e.g., full table scans, inefficient joins) and optimizing your queries by adding indexes or rewriting the SQL.

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
ANALYZE SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Analyzing query execution plans for optimization.

12. Data Partitioning and Indexing Strategies

While not strictly SQL syntax, understanding how data partitioning (splitting large tables into smaller, more manageable pieces) and effective indexing strategies work within your database system is crucial for performance optimization, especially with large datasets.

Database-level optimization techniques.

13. Using Materialized Views for Pre-computation

Materialized views store the results of a query in advance. For frequently executed, complex queries, using a materialized view can significantly improve performance by serving pre-computed data instead of running the query every time.

-- Example of creating a materialized view (syntax varies)
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    EXTRACT(YEAR FROM order_date) AS sales_year,
    EXTRACT(MONTH FROM order_date) AS sales_month,
    SUM(amount) AS total_sales
FROM
    orders
GROUP BY
    sales_year, sales_month;

-- Refreshing the materialized view to update data
REFRESH MATERIALIZED VIEW monthly_sales_summary;

Pre-computing and storing query results for faster access.

14. Common Table Expressions for Data Masking or Anonymization

CTEs can be used to perform data masking or anonymization techniques before the final result set is returned, ensuring data privacy while still allowing for analysis.

WITH AnonymizedCustomers AS (
    SELECT
        customer_id,
        'CUSTOMER_' || SUBSTR(CAST(customer_id AS VARCHAR), -4) AS anonymized_id,
        -- More complex anonymization techniques can be applied here
        CASE
            WHEN LENGTH(customer_name) > 5 THEN SUBSTR(customer_name, 1, 2) || '***'
            ELSE 'PRIVATE'
        END AS anonymized_name
    FROM
        customers
)
SELECT anonymized_id, anonymized_name FROM AnonymizedCustomers;

Applying data masking techniques within the query.

15. Understanding Set Operations (UNION, INTERSECT, EXCEPT)

Set operations allow you to combine the results of multiple SELECT statements. Understanding the behavior and performance implications of UNION (all rows), UNION ALL (all rows including duplicates), INTERSECT (common rows), and EXCEPT (rows in the first set but not the second) is crucial for complex data manipulation.

-- Find all customers who have either placed an order or have a support ticket
SELECT customer_id FROM orders
UNION
SELECT customer_id FROM support_tickets;

-- Find customers who have both placed an order and have a support ticket
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM support_tickets;

Combining and comparing result sets from multiple queries.