The Complete SQL Reference Guide 2026: SELECT, JOINs, Window Functions, CTEs, and Query Optimization

Executive Summary

SQL remains the universal language of data in 2026. Despite decades of NoSQL alternatives, SQL databases power 62% of all applications, and PostgreSQL has become the most popular database among developers, surpassing MySQL and Oracle. The SQL standard continues to evolve with SQL:2023 adding JSON improvements, property graph queries, and enhanced window functions. Meanwhile, DuckDB has emerged as the fastest-growing database for analytics, bringing columnar SQL to embedded applications.

This report covers every major SQL concept from the basics (SELECT, WHERE, JOIN) through advanced features (window functions, recursive CTEs, lateral joins) to practical concerns (indexing, query optimization, transactions, normalization). Every section includes reference tables with 100+ SQL functions, comparison data, and practical examples that work across PostgreSQL, MySQL, and SQLite. Whether you are writing your first query or optimizing production databases, this is the single SQL reference you need.

• PostgreSQL holds 30% market share, becoming the most popular database among developers for the third consecutive year. Its extensibility (pgvector for AI, PostGIS for spatial, JSONB for documents) makes it the "Swiss Army knife" of databases.
• DuckDB grew from 0% to 5% share in 4 years, becoming the go-to analytical database for data scientists. Its zero-configuration, in-process architecture and columnar storage bring OLAP performance to any application.
• SQL databases hold 62% of the market, with hybrid approaches (SQL databases with JSON/document support) growing to 11%. The SQL vs NoSQL debate has shifted to "use the right tool for the job."
• Window functions and CTEs are now universally supported across all major databases (PostgreSQL, MySQL 8+, SQLite 3.25+, SQL Server), enabling analytical queries that previously required application-level code or stored procedures.

Part 1: SELECT and WHERE

The SELECT statement is the foundation of SQL. It retrieves data from one or more tables. The basic syntax is: SELECT columns FROM table WHERE condition ORDER BY column LIMIT n. Every SQL query, no matter how complex, builds upon these fundamental clauses. Understanding their execution order is critical: FROM (identify tables) -> WHERE (filter rows) -> GROUP BY (group rows) -> HAVING (filter groups) -> SELECT (choose columns) -> ORDER BY (sort results) -> LIMIT (restrict output).

The WHERE clause filters rows before any grouping occurs. It supports: comparison operators (=, !=, <, >, <=, >=), logical operators (AND, OR, NOT), pattern matching (LIKE, ILIKE for case-insensitive), range checking (BETWEEN x AND y), set membership (IN (value1, value2)), NULL checking (IS NULL, IS NOT NULL), and existence testing (EXISTS). Combining these with parentheses for operator precedence enables arbitrarily complex filtering. Performance tip: put the most selective conditions first, and ensure filtered columns have indexes.

SELECT DISTINCT removes duplicate rows from results. SELECT * retrieves all columns (avoid in production code; always list specific columns for clarity, performance, and schema change safety). Column aliases (AS alias_name) rename output columns. Expression columns allow computed values: SELECT price * quantity AS total. CASE expressions provide conditional logic inline: SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade.

ORDER BY sorts results by one or more columns in ascending (ASC, default) or descending (DESC) order. NULLS FIRST / NULLS LAST controls NULL positioning (PostgreSQL). LIMIT restricts the number of rows returned. OFFSET skips rows (avoid for large offsets; use cursor-based pagination instead). PostgreSQL and SQLite use LIMIT/OFFSET; MySQL uses LIMIT/OFFSET; SQL Server uses TOP or OFFSET-FETCH.

Part 2: JOIN Types

JOINs combine rows from two or more tables based on a related column. They are the primary mechanism for querying data spread across normalized tables. Understanding when to use each JOIN type is one of the most important SQL skills. The JOIN condition (ON clause) specifies how tables relate; it typically matches a foreign key to a primary key.

INNER JOIN returns only rows where the join condition is satisfied in both tables. If a customer has no orders, they will not appear in the result. LEFT JOIN (LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right; unmatched right-side columns are filled with NULL. This is the most common join when you want "all X, with optional Y." RIGHT JOIN is the mirror of LEFT JOIN; it is rarely used because you can always rewrite it as a LEFT JOIN with the tables swapped.

FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match on either side. CROSS JOIN produces the Cartesian product (every row from table A combined with every row from table B). Use CROSS JOIN for generating combinations (all sizes x all colors) but be cautious of the result set size (n * m rows). SELF JOIN joins a table with itself using aliases, useful for hierarchical data (employees/managers) and comparing rows within the same table.

Part 3: GROUP BY and HAVING

GROUP BY groups rows sharing common values into summary rows, enabling aggregate calculations per group. Every non-aggregated column in the SELECT clause must appear in the GROUP BY clause (standard SQL; MySQL historically allowed non-deterministic results). Aggregate functions (COUNT, SUM, AVG, MIN, MAX) operate on each group independently. Without GROUP BY, aggregate functions operate on the entire result set as one group.

HAVING filters groups after aggregation (unlike WHERE, which filters individual rows before grouping). HAVING can use aggregate functions: HAVING COUNT(*) > 5 keeps only groups with more than 5 rows. WHERE cannot reference aggregate functions. Performance tip: filter as much as possible with WHERE (before grouping) rather than HAVING (after grouping), because WHERE reduces the number of rows that need to be grouped.

Advanced GROUP BY extensions: ROLLUP generates subtotals and a grand total (GROUP BY ROLLUP(department, city) produces totals by department+city, by department, and overall). CUBE generates subtotals for all combinations. GROUPING SETS specifies exactly which groupings to compute. The GROUPING() function distinguishes NULL values from actual NULLs in rollup rows. These extensions are essential for reporting and business intelligence queries.

Part 4: Subqueries

A subquery is a SELECT statement nested inside another SQL statement. Subqueries can appear in the WHERE clause (most common), the FROM clause (derived tables), and the SELECT clause (scalar subqueries). Subqueries are classified by their return type: scalar (single value), row (single row, multiple columns), and table (multiple rows and columns). They can also be correlated (reference the outer query) or non-correlated (independent).

Non-correlated subqueries execute once and their result is used by the outer query. Example: SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products). Correlated subqueries reference the outer query and execute once for each outer row. Example: SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id). Correlated subqueries can be expensive; consider rewriting as JOINs.

EXISTS is often the most efficient way to test for the existence of related rows: WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id). EXISTS stops at the first match (short-circuits), making it faster than COUNT(*) > 0. IN vs EXISTS: IN materializes the subquery result into a set; EXISTS checks row by row. For large subquery results, EXISTS is usually faster. NOT EXISTS and NOT IN differ in NULL handling: NOT IN returns no rows if the subquery contains NULL; NOT EXISTS handles NULLs correctly.

Part 5: Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row (unlike GROUP BY). The syntax is: function() OVER (PARTITION BY ... ORDER BY ... frame_clause). Window functions are computed after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT. They enable analytical queries that are impossible or extremely awkward with standard GROUP BY.

Ranking functions: ROW_NUMBER() assigns a unique sequential number (1, 2, 3, ...) to each row within a partition. RANK() assigns the same rank to ties, leaving gaps (1, 2, 2, 4). DENSE_RANK() assigns the same rank to ties without gaps (1, 2, 2, 3). NTILE(n) distributes rows into n groups. These are essential for top-N queries, pagination, and deduplication.

Value functions: LAG(column, offset) accesses data from a previous row (previous month sales, previous day price). LEAD(column, offset) accesses data from a subsequent row. FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() return values from specific positions within the window frame. These enable period-over-period comparisons without self-joins.

Aggregate window functions: SUM(), AVG(), COUNT(), MIN(), MAX() can all be used as window functions with OVER(). SUM(amount) OVER (ORDER BY date) produces a running total. AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) produces a 7-day moving average. The frame clause (ROWS BETWEEN ... AND ...) controls which rows are included in the calculation relative to the current row.

Part 6: Common Table Expressions (CTEs)

CTEs are temporary named result sets defined with the WITH keyword. They improve query readability by breaking complex queries into logical steps. Syntax: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name. Multiple CTEs can be chained: WITH cte1 AS (...), cte2 AS (SELECT * FROM cte1 ...) SELECT * FROM cte2. CTEs are not materialized by default in PostgreSQL (the optimizer may inline them); add MATERIALIZED hint to force materialization.

Recursive CTEs (WITH RECURSIVE) enable traversing hierarchical and graph data. Structure: WITH RECURSIVE tree AS (base_case UNION ALL recursive_step) SELECT * FROM tree. The base case provides the starting rows; the recursive step references the CTE itself to add more rows. Each iteration adds new rows until the recursive step returns no rows. Common uses: organizational hierarchies (employee/manager), category trees, bill of materials, finding all connected nodes in a graph, and generating number series.

Practical CTE patterns: (1) Readability: name each logical step (WITH active_users AS ..., recent_orders AS ... SELECT ...). (2) Deduplication: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM users) SELECT * FROM ranked WHERE rn = 1. (3) Recursive hierarchy: WITH RECURSIVE org AS (SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, o.level + 1 FROM employees e JOIN org o ON e.manager_id = o.id) SELECT * FROM org.

Part 7: Indexes

Indexes are data structures that speed up data retrieval at the cost of additional storage and slower writes. Without an index, the database must scan every row (sequential scan) to find matching data. With a B-Tree index, lookup is O(log n) instead of O(n). The trade-off: every INSERT, UPDATE, and DELETE must also update all relevant indexes, so over-indexing slows down write operations.

Index strategy: (1) Index columns used in WHERE clauses (equality and range conditions). (2) Index columns used in JOIN conditions. (3) Index columns used in ORDER BY to avoid sorts. (4) Use composite indexes for multi-column queries (column order matters: leftmost prefix rule). (5) Consider partial indexes for queries that filter on a specific condition (CREATE INDEX ON orders(user_id) WHERE status = 'active'). (6) Use covering indexes (INCLUDE) to avoid table lookups. (7) Do not index columns with low cardinality (boolean, status with few values) unless combined with selective columns.

Part 8: Transactions and ACID

A transaction is a sequence of SQL operations treated as a single atomic unit of work. Transactions ensure data integrity: either all operations succeed (COMMIT) or none of them take effect (ROLLBACK). The ACID properties (Atomicity, Consistency, Isolation, Durability) define the guarantees that a database transaction provides. Understanding ACID is essential for any application that modifies data.

Isolation levels control how concurrent transactions interact. READ UNCOMMITTED allows dirty reads (seeing uncommitted changes). READ COMMITTED (PostgreSQL default) only sees committed data. REPEATABLE READ (MySQL InnoDB default) provides a consistent snapshot for the entire transaction. SERIALIZABLE provides full isolation at the cost of potential serialization failures. Choose the lowest isolation level that maintains correctness for your use case to maximize concurrency.

Part 9: Stored Procedures and Triggers

Stored procedures are precompiled SQL code blocks stored in the database and executed with CALL procedure_name(). They encapsulate business logic, reduce network traffic (one call instead of many queries), and enforce data integrity at the database level. Stored procedures support variables, control flow (IF/ELSE, LOOP, WHILE), error handling (EXCEPTION in PostgreSQL, DECLARE HANDLER in MySQL), and cursors. However, they are harder to version control, test, and debug than application code.

Triggers are procedural code blocks that automatically execute in response to table events (INSERT, UPDATE, DELETE). They fire BEFORE or AFTER the event, and FOR EACH ROW or FOR EACH STATEMENT. Common uses: audit logging (record who changed what and when), maintaining computed columns (update a denormalized total when line items change), enforcing complex constraints (business rules that cannot be expressed as CHECK constraints), and synchronizing related tables. Overuse of triggers makes debugging difficult because they execute implicitly.

User-Defined Functions (UDFs) differ from stored procedures in that they return a value and can be used in SELECT statements. Scalar functions return a single value. Table-valued functions return a result set. PostgreSQL supports functions in PL/pgSQL, SQL, Python (PL/Python), and JavaScript (PL/v8). MySQL supports functions in SQL and C. UDFs enable reusable calculations: CREATE FUNCTION discount_price(price DECIMAL, discount DECIMAL) RETURNS DECIMAL AS $$ SELECT price * (1 - discount) $$;

Part 10: Normalization

Normalization is the process of organizing data to reduce redundancy and improve data integrity. Each normal form builds on the previous one, adding stricter rules. Most production databases should be in Third Normal Form (3NF). Over-normalization leads to excessive JOINs and slower reads; strategic denormalization improves performance for read-heavy workloads.

Part 11: Query Optimization

Query optimization is the process of making SQL queries run faster. The systematic approach: (1) Identify slow queries (log queries exceeding a threshold). (2) Run EXPLAIN ANALYZE to see the execution plan. (3) Look for sequential scans on large tables (add an index). (4) Check estimated vs actual row counts (large differences mean stale statistics; run ANALYZE). (5) Identify expensive operations (sorts, hash joins on large tables). (6) Rewrite the query if possible (subqueries to JOINs, add CTEs for readability without performance cost).

Common optimization patterns: (1) Index WHERE and JOIN columns. (2) Use LIMIT with ORDER BY for pagination (and cursor-based pagination for large offsets). (3) Avoid SELECT * (list specific columns). (4) Use EXISTS instead of COUNT(*) > 0 for existence checks. (5) Batch INSERT operations. (6) Use prepared statements for repeated queries. (7) Consider materialized views for expensive aggregations. (8) Partition large tables by date or category. (9) Use connection pooling (PgBouncer, ProxySQL). (10) Monitor and tune database configuration parameters (shared_buffers, work_mem, effective_cache_size).

Anti-patterns to avoid: (1) N+1 query problem (use JOINs or eager loading). (2) Using OFFSET for deep pagination (use cursor/keyset pagination). (3) Implicit type conversions in WHERE (prevents index usage). (4) Functions on indexed columns in WHERE (prevents index usage; use expression indexes instead). (5) Missing VACUUM/ANALYZE (stale statistics lead to bad plans). (6) Over-indexing (slows writes, wastes storage). (7) Using ORM without monitoring generated SQL.

Part 12: SQL Functions Reference (100+)

A comprehensive, searchable reference of 100+ SQL functions covering aggregate, string, date/time, math, conditional, conversion, window, subquery, JSON, and utility functions across PostgreSQL, MySQL, SQLite, and SQL Server.

Glossary (50+ Terms)

FAQ (20 Questions)

Try It Yourself

Use these embedded tools to format and build SQL queries discussed in this guide.

Raw Data Downloads

Citations and Sources

Related Articles and Tools

Related Research Reports