When basic SELECT and JOIN no longer handle the tasks, it's time for advanced SQL. Window functions, recursive CTEs, and proper query optimization are not just 'tricks' but tools that transform your code from slow powerlifting into an Olympic sprint. In this article, we'll explore how to elevate your SQL proficiency and speed up database work without unnecessary resource waste.
Why 'Simple SQL' is a Thing of the Past?
Modern analysts and developers deal with terabytes of data. A simple WHERE filter and GROUP BY often lead to temporary table overflow and timeouts. Advanced SQL allows you to:
- Reduce query execution time from minutes to seconds.
- Decrease CPU and memory load on the server.
- Write concise solutions for complex reports, rankings, and analytics.
The key to this is understanding the internal workings of the DBMS, effective indexes, and the query execution plan.
Window Functions: Calculations Without Subqueries
Window functions (WINDOW) are a must-have for analysts. They allow you to compute running totals, ranks, and aggregates without losing row-level detail.
Example: we want to get the average salary by department but keep each employee row:
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;
Popular window functions:
| Function | Purpose |
|---|---|
| ROW_NUMBER() | Number rows within a group |
| RANK() / DENSE_RANK() | Ranking with/without gaps |
| LAG() / LEAD() | Access previous/next row |
| SUM()/AVG() OVER | Aggregation with a window |
With window functions, you can easily build a cumulative sales total or compare current month metrics to the previous one—all in a single query.
CTEs: Readable Code and Recursion
Common Table Expressions (CTEs) are named temporary result sets. They make queries modular and understandable. Recursive CTEs are especially useful for traversing hierarchies (product categories, organizational structures).
Simple non-recursive CTE:
WITH high_salary AS (
SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*)
FROM high_salary
GROUP BY department;
Recursive CTE for an employee tree:
WITH RECURSIVE org_tree AS (
SELECT id, manager_id, name, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, t.level + 1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;
Using CTEs improves code readability and simplifies debugging, which is critical when working with multi-step reports.
Indexes: How to Speed Up Search by 100x
Indexes are data structures (usually B-trees) that allow the DBMS to find rows without a full table scan. Without them, any query with a filter will scan millions of records.
Main types of indexes:
- B-tree — universal, suitable for exact search and ranges.
- Hash — only for equality (WHERE id = 5).
- GiST / GIN — for full-text search and JSON.
Tips for creating indexes:
- Index columns that frequently appear in WHERE, JOIN, and ORDER BY.
- Avoid too many indexes on one table (they slow down inserts).
- Use composite indexes for filtering by multiple columns.
- Regularly analyze index usage with
EXPLAIN ANALYZE.
Query Plans: Reading the DBMS's Mind
The EXPLAIN (ANALYZE) command shows how the database executes your query: which indexes are used, how many rows are processed, whether there is disk sorting.
Example analysis:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
In the plan, you will see:
- Seq Scan — full scan
Comments