15 Advanced SQL Concepts With Examples (2026 Guide)

Basic SQL gets you filtering, sorting, and simple joins. Advanced SQL is where you turn raw tables into analysis: ranking rows, walking hierarchies, reshaping data between rows and columns, and making queries run fast at scale. This 2026 guide covers 15 advanced SQL concepts, and every one comes with a query you can actually run.

The concepts are grouped into four themes so you can find what you need: combining data, analytics and reshaping, logic and transformation, and performance. If you are learning, work top to bottom. If you are looking something up, jump to the cheat sheet below.

What Is Advanced SQL?

Advanced SQL refers to the techniques that go beyond selecting, filtering, and sorting single tables. It includes window functions, common table expressions, recursion, pivoting, user defined functions, and the performance work that keeps those queries responsive. These are the skills that separate someone who can read data from someone who can model and transform it.

A quick note on dialects. SQL is a standard, but every engine adds its own syntax. The examples below use portable ANSI syntax where possible, and call out where Postgres, MySQL, and SQL Server differ so you are not caught out.

Advanced SQL Concepts Cheat Sheet

Use this as a fast reference. Each concept is explained with an example further down.

Part 1: Combining and Querying Data

1. Subqueries

A subquery is a complete query nested inside another. It lets you break a hard question into smaller parts. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses. A correlated subquery references the outer query and runs once per outer row.

This finds every customer who has placed at least one order over 500:

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

A correlated subquery can return a per row value, such as each customer's order count:

SELECT c.customer_name,
       (SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

2. Advanced Joins

Joins are the bridge between tables. Beyond the standard INNER and LEFT joins, two patterns matter for advanced work: the self join, where a table joins to itself, and the FULL join, which keeps unmatched rows from both sides.

A self join resolves each employee to their manager from a single employees table:

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Here is how the join types differ:

3. Set Operations

Set operations stack the results of two queries that share the same columns. UNION removes duplicates, UNION ALL keeps them, INTERSECT returns rows common to both, and EXCEPT returns rows in the first query but not the second.

SELECT customer_id FROM orders WHERE status = 'shipped'
EXCEPT
SELECT customer_id FROM orders WHERE status = 'returned';

Dialect note: MySQL added INTERSECT and EXCEPT in version 8.0.31. On older MySQL you emulate them with joins or the IN and NOT IN operators.

4. Common Table Expressions (CTEs)

A CTE is a named, temporary result set defined with the WITH keyword and referenced later in the same statement. CTEs make long queries readable, can be referenced more than once, and are the foundation for recursion.

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_name, t.total_spent
FROM customer_totals t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.total_spent > 1000;

5. Recursive Queries

A recursive query repeatedly runs against its own output until a stop condition is met. It is the standard tool for hierarchical data: org charts, folder trees, and bills of materials. A recursive CTE always has three parts: a base case, a recursive step joined back to the CTE, and a termination that happens when the recursive step returns no more rows.

WITH RECURSIVE org_chart AS (
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT employee_id, employee_name, level
FROM org_chart
ORDER BY level;

Dialect note: Postgres, MySQL, and SQLite require the RECURSIVE keyword. SQL Server uses the same WITH syntax but omits RECURSIVE.

Part 2: Analytics and Reshaping

6. Aggregate Functions

Aggregate functions collapse many rows into one summary value. COUNT, SUM, AVG, MIN, and MAX are the core set, and they pair with GROUP BY to summarize per group. HAVING filters those groups after aggregation.

SELECT p.category,
       COUNT(*) AS order_lines,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       AVG(oi.unit_price) AS avg_price
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > 10000;

7. Window Functions

Window functions perform a calculation across a set of rows related to the current row, without collapsing them the way GROUP BY does. This is how you rank rows, compare a row to its neighbors, and build running totals while still seeing every row. Common functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and any aggregate used with OVER.

This ranks each customer's orders and builds a running total in one pass:

SELECT customer_id,
       order_id,
       amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY amount DESC) AS rank_in_customer,
       SUM(amount) OVER (PARTITION BY customer_id
                         ORDER BY order_date) AS running_total
FROM orders;

LAG compares each row to the previous one, which is ideal for period over period change:

SELECT order_date,
       amount,
       LAG(amount) OVER (ORDER BY order_date) AS previous_amount
FROM orders;

Rule of thumb: Use GROUP BY when you want one row per group. Use a window function when you want the aggregate alongside the original detail rows.

8. Pivoting and Unpivoting

Pivoting turns row values into columns, which is how you build cross tabs such as sales by product across months. The most portable approach uses CASE inside an aggregate, so it runs on any engine.

SELECT oi.product_id,
       SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 1
                THEN oi.quantity ELSE 0 END) AS jan,
       SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 2
                THEN oi.quantity ELSE 0 END) AS feb,
       SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 3
                THEN oi.quantity ELSE 0 END) AS mar
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
GROUP BY oi.product_id;

Dialect note: SQL Server offers a dedicated PIVOT and UNPIVOT operator. Postgres provides the crosstab function in the tablefunc extension. The CASE pattern above works everywhere.

Part 3: Logic and Transformation

9. CASE Expressions

CASE brings conditional logic into a query, working like an if then else that returns a value. It is the workhorse for bucketing, relabeling, and conditional aggregation.

SELECT customer_id,
       SUM(amount) AS total_spent,
       CASE
           WHEN SUM(amount) >= 5000 THEN 'VIP'
           WHEN SUM(amount) >= 1000 THEN 'Regular'
           ELSE 'Occasional'
       END AS segment
FROM orders
GROUP BY customer_id;

10. String Functions

String functions clean and reshape text, a constant need in data preparation. CONCAT joins values, UPPER and LOWER standardize case, and SUBSTRING extracts part of a value.

SELECT customer_id,
       UPPER(customer_name) AS name_upper,
       CONCAT(customer_name, ' (', country, ')') AS label,
       SUBSTRING(customer_name FROM 1 FOR 3) AS short_code
FROM customers;

Dialect note: For substrings, Postgres accepts SUBSTRING(col FROM 1 FOR 3), while MySQL and SQL Server use SUBSTRING(col, 1, 3). SQL Server concatenates with the plus operator or CONCAT.

11. Date and Time Functions

Date functions let you extract parts of a timestamp, truncate to a period, and measure spans between dates. These power time series analysis and cohort reporting.

SELECT order_id,
       EXTRACT(YEAR FROM order_date) AS order_year,
       DATE_TRUNC('month', order_date) AS order_month,
       DATEDIFF(day, order_date, CURRENT_DATE) AS days_since_order
FROM orders;

Dialect note: EXTRACT and DATE_TRUNC are Postgres style. SQL Server uses DATEPART and DATEDIFF. MySQL uses EXTRACT plus DATEDIFF with two arguments. Check your engine before shipping date logic.

12. User Defined Functions

A user defined function packages logic you reuse across queries, much like a function in any programming language. Scalar functions return one value, while table valued functions return a result set.

This Postgres function returns the discount amount for a given price and rate:

CREATE FUNCTION discount_amount(price NUMERIC, rate NUMERIC)
RETURNS NUMERIC AS $$
    SELECT price * rate;
$$ LANGUAGE SQL;

SELECT product_name, discount_amount(price, 0.10) AS savings
FROM products;

Part 4: Performance and Database Objects

13. Temporary Tables

A temporary table holds interim results for the duration of a session, then disappears. It is useful when a multi step transformation needs a scratch space, or when the same intermediate result is queried several times.

CREATE TEMP TABLE high_value_orders AS
SELECT order_id, customer_id, amount
FROM orders
WHERE amount > 1000;

SELECT customer_id, COUNT(*) AS big_orders
FROM high_value_orders
GROUP BY customer_id;

Dialect note: Postgres and MySQL use CREATE TEMP TABLE or CREATE TEMPORARY TABLE. SQL Server prefixes the name with a hash sign, as in a table called hash orders.

14. Indexing and Query Optimization

Optimization is about doing less work. Indexes let the engine find rows without scanning the whole table, and the EXPLAIN command shows you the plan the engine chose so you can spot full scans and missing indexes.

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN
SELECT customer_id, SUM(amount)
FROM orders
WHERE customer_id = 42
GROUP BY customer_id;

Beyond indexing, the biggest wins usually come from selecting only the columns you need, filtering early, and avoiding functions on indexed columns in the WHERE clause, which can stop an index from being used.

15. Predicate Pushdown (External Query Filter)

Predicate pushdown, sometimes called filter pushdown or an external query filter, is an optimization where the engine sends filter conditions down to the data source so that only matching rows are read and transferred. This matters most for federated queries and query engines over data lakes, where moving fewer rows across the network is a large saving.

SELECT o.order_id, o.amount
FROM external_orders o
WHERE o.status = 'shipped'
  AND o.amount > 1000;

When pushdown works, the WHERE conditions run at the remote source rather than after the data arrives, so the query reads a fraction of the rows.

GROUP BY vs Window Functions

These two are often confused. The difference is whether detail rows survive.

Centralize Your Data Before You Query It With Airbyte

Advanced SQL is only as good as the data it runs on. Before you can join, aggregate, and rank, the data from your applications, databases, and files has to land in one place. That is where a data integration platform like Airbyte fits in.

Airbyte uses connectors and pipelines to support modern ELT, moving data from many sources into your warehouse where SQL can do the rest. Key features include:

• A library of over 600 connectors covering SaaS apps, databases, and files, so most sources need no custom work.
• A Connector Development Kit for building your own connector when a source is not yet covered.
• Flexible sync modes including full refresh, incremental, and Change Data Capture for efficient movement.

Conclusion

These 15 concepts cover the techniques that turn SQL from a lookup tool into an analysis engine. The fastest way to internalize them is to run the examples against your own tables and adapt them. Start with window functions and CTEs, which unlock the most, then layer in recursion, pivoting, and optimization as your queries grow.

Frequently Asked Questions About Advanced SQL

What counts as advanced SQL compared to basics?

Advanced SQL goes past simple selects and filters to include window functions, CTEs, recursion, pivoting, complex and self joins, user defined functions, and performance tuning. The shift is from reading data to modeling and transforming it.

When should I use a window function instead of GROUP BY?

Use a window function when you need an aggregate alongside the original rows, such as showing each order next to the customer's running total. GROUP BY collapses rows into one per group, while a window function keeps every row visible.

How do CTEs differ from subqueries?

CTEs are named and defined once at the top of a statement, which makes long queries readable and lets you reference the same result more than once. They also support recursion. Subqueries are inline and better for short, one off logic.

When do I use recursive CTEs?

Use them for hierarchies and paths: org charts, folder trees, bills of materials, and graph traversals. Always include a base case, a recursive step, and a condition that eventually stops the recursion.

What are the most important advanced SQL concepts to learn first?

Window functions and CTEs give the biggest return, because they appear constantly in analytics work and make complex queries manageable. Learn those, then add recursion, pivoting, and indexing.

Are window functions faster than subqueries?

Often yes, because a window function can compute a result in a single pass over the data, while a correlated subquery may run once per row. Always confirm with EXPLAIN on your own data, since the engine and indexes matter.

How do I practice advanced SQL?

Build a small sample schema like the customers, orders, and products tables used here, then rewrite real questions from your work as queries. Reading EXPLAIN output on your own queries teaches optimization faster than any tutorial.

Is advanced SQL still relevant now that AI can write queries?

Yes. AI assistants can draft queries quickly, but you still need to read, verify, and tune them, and to know when a result is wrong. Understanding window functions, joins, and query plans is what lets you trust or correct generated SQL.