As your SQL skills evolve beyond basic data retrieval and manipulation, you’ll need to master advanced SQL query techniques. These techniques empower you to handle complex business logic, combine multiple datasets, improve performance, and extract valuable insights from vast datasets.
Table of Contents
In this chapter, we’ll explore a wide range of advanced SQL features: multi-table joins, subqueries, set operators, window functions, common table expressions (CTEs), pivoting, and performance tuning strategies. Each concept will be explained with examples and best practices.
Multi-Table Joins – Combining Data Across Relations
Joins enable querying data spread across multiple related tables.
INNER JOIN
Returns only rows with matching values in both tables.
SELECT o.OrderID, c.Name
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
LEFT JOIN
Returns all rows from the left table, and matched rows from the right table. NULLs for unmatched.
SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID;
RIGHT JOIN
Returns all rows from the right table, and matched rows from the left.
SELECT e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DeptID = d.DeptID;
FULL OUTER JOIN
Returns rows from both tables, matching where possible.
SELECT a.ID, b.Value
FROM TableA a
FULL OUTER JOIN TableB b ON a.ID = b.ID;
CROSS JOIN
Returns Cartesian product (all combinations).
SELECT * FROM Colors CROSS JOIN Sizes;
Use with caution – output grows rapidly.
Subqueries – Nesting SELECT Statements
Subqueries (or nested queries) are queries within queries. They can return:
- A single value (scalar)
- A row or multiple rows
- A full table
Subquery in WHERE
SELECT Name FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
Subquery in FROM
SELECT Region, AVG(Sales) FROM (
SELECT Region, SUM(Amount) AS Sales FROM Transactions GROUP BY Region
) AS RegionalSales
GROUP BY Region;
Correlated Subquery
Executes once per outer row.
SELECT Name FROM Employees e
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE Department = e.Department
);
Subquery with EXISTS
SELECT * FROM Customers c
WHERE EXISTS (
SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);
Tip: Use EXISTS for better performance over IN in large subqueries.
Set Operators – Combining Query Results
Set operators combine results from multiple SELECT queries.
UNION
Combines results and removes duplicates.
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;
UNION ALL
Includes duplicates.
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;
INTERSECT
Returns common rows.
SELECT City FROM Customers
INTERSECT
SELECT City FROM Suppliers;
EXCEPT (or MINUS in Oracle)
Returns rows in first set, not in second.
SELECT City FROM Customers
EXCEPT
SELECT City FROM Suppliers;
Set operators require:
- Same number of columns
- Same data types (compatible)
- Column order alignment
Common Table Expressions (CTEs)
CTEs make queries more readable and reusable.
Basic CTE
WITH SalesByRegion AS (
SELECT Region, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region
)
SELECT * FROM SalesByRegion WHERE TotalSales > 10000;
Recursive CTE
Useful for hierarchical data like org charts or category trees.
WITH RECURSIVE OrgChart AS (
SELECT EmployeeID, ManagerID, Name, 1 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Name, Level + 1
FROM Employees e
JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart;
Benefits of CTEs:
- Break complex queries into steps
- Avoid repeating subqueries
- Enable recursion
Window Functions – Row-Level Analysis
Window functions perform calculations across sets of rows related to the current row.
Syntax:
function() OVER (PARTITION BY column ORDER BY column)
Examples:
SELECT Name, Department, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank
FROM Employees;
SELECT CustomerID, OrderDate,
LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousOrder
FROM Orders;
Common Window Functions:
ROW_NUMBER()RANK()/DENSE_RANK()LAG()/LEAD()SUM(),AVG(),COUNT()(as window aggregates)
Pivoting and Unpivoting Data
Pivot
Transforms rows into columns.
SELECT * FROM (
SELECT Month, Product, Revenue FROM Sales
) AS SourceTable
PIVOT (
SUM(Revenue) FOR Month IN ([Jan], [Feb], [Mar])
) AS PivotTable;
(Not all RDBMS support PIVOT; use conditional aggregation as an alternative.)
Unpivot
Turns columns into rows.
SELECT Product, Month, Revenue
FROM PivotTable
UNPIVOT (
Revenue FOR Month IN ([Jan], [Feb], [Mar])
) AS Unpivoted;
Performance Optimization Techniques
- Use Indexes – For WHERE, JOIN, and ORDER BY columns
- Avoid Functions on Indexed Columns –
WHERE UPPER(Name)disables index - Minimize Subqueries – Use joins or CTEs where possible
- Use
EXISTSInstead ofINfor large subquery datasets - Use Batching/Paging – For large queries, use
LIMITandOFFSET - Profile Your Queries – Use
EXPLAIN,ANALYZE, or tools like SQL Profiler - Materialized Views – Precompute expensive results (in supported databases)
Real-World Scenario: Marketing Campaign Analysis
Assume you’re analyzing email campaign performance using tables:
Campaigns (CampaignID, Name, StartDate)Recipients (RecipientID, CampaignID, EmailSent, Opened, Clicked)
Query 1: CTR by Campaign
SELECT c.Name,
ROUND(SUM(r.Clicked)::decimal / SUM(r.EmailSent) * 100, 2) AS CTR
FROM Campaigns c
JOIN Recipients r ON c.CampaignID = r.CampaignID
GROUP BY c.Name;
Query 2: Recipients with Declining Engagement
WITH Engagement AS (
SELECT RecipientID,
SUM(Opened) AS TotalOpens,
SUM(Clicked) AS TotalClicks
FROM Recipients
GROUP BY RecipientID
)
SELECT * FROM Engagement WHERE TotalClicks < 2 AND TotalOpens < 5;
Query 3: Last Open Per User
SELECT RecipientID, MAX(OpenedDate) AS LastOpen
FROM EmailOpens
GROUP BY RecipientID;
Common Pitfalls in Advanced SQL
| Pitfall | Solution |
|---|---|
| Using too many nested subqueries | Use CTEs or flatten with joins |
| Non-SARGable WHERE conditions | Avoid functions on indexed columns |
| Incorrect JOIN type or condition | Always verify join keys and relationships |
| Overuse of DISTINCT or ORDER BY | Can hurt performance; use only when needed |
| Writing unreadable long queries | Break into steps using CTEs |
Summary
Advanced SQL gives you the tools to go beyond simple CRUD operations and develop sophisticated queries for real-world applications.
You now have:
- Deep understanding of multi-table joins
- Ability to craft optimized subqueries
- Skill to combine results using set operators
- Power of recursive and analytical queries with CTEs and window functions
- Query optimization tactics to enhance performance
In the next chapter, we will focus entirely on SQL Functions, exploring built-in aggregate, scalar, date/time, string, and custom functions to transform and analyze your data more effectively.