What is a SQL query? How SELECT, JOIN and aggregation work

A SQL query is a declarative statement you send to a relational database to ask for data. You describe what you want — a result set of rows matching some criteria — and the database engine decides how to compute it. The most common query is the SELECT statement: it has no persistent effect on the data and is read-only, which is why it is the workhorse of analytics, reporting, dashboards, and almost every application's data layer. The same database that runs your queries also stores them; the language is "Structured Query Language" (pronounced "ess-cue-ell" or "sequel") and the standard that defines it is ISO/IEC 9075, last revised in 2023.

SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s, after they learned about Edgar F. Codd's relational model from his 1970 paper, "A Relational Model of Data for Large Shared Data Banks." The first version, originally called SEQUEL, was designed to manipulate and retrieve data from IBM's System R prototype. The language became an ANSI standard in 1986 and an ISO standard in 1987, and has been revised in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011, 2016, and most recently 2023. Despite those standards, virtually no implementation adheres to the full specification — most SQL code requires at least small changes before being ported between database systems, which is why cross-database libraries (ORMs, query builders) remain popular.

What a SQL query does

A query reads from one or more tables and returns a result set of rows and columns. It does not change the data; that is the job of INSERT, UPDATE, and DELETE. A query can be nested inside another query (a subquery) and can reference a previous query in the same FROM clause (a derived table or inline view). Since SQL:1999 you can also name a subquery with a WITH clause — a common table expression (CTE) — and CTEs can reference themselves recursively, which is how SQL implements tree and graph traversals.

Three properties distinguish a query from a procedure:

• Declarative: you describe the desired result, not the steps to produce it. The database's query optimizer chooses a plan.
• Set-based: the result is a set (or multi-set) of rows, not a stream of records to be processed one at a time. The same row may appear more than once unless you ask for DISTINCT.
• Closed under composition: the output of a query is itself a table, so you can pipe one query into another, nest them, or join the result of one query against a real table.

The SELECT statement

The SELECT statement has several optional clauses. The full syntax (in the order they are evaluated) is:

SELECT [DISTINCT] select_list
FROM   table_source
[WHERE predicate]
[GROUP BY columns]
[HAVING group_predicate]
[ORDER BY columns [ASC | DESC]]
[LIMIT n | FETCH FIRST n ROWS ONLY];

Each clause has a distinct job:

• SELECT — the columns or expressions to return. SELECT * returns every column. SELECT count(*) is an aggregate expression, not a column reference.
• FROM — the table or tables the data comes from. With more than one table and no join condition, you get a Cartesian product.
• WHERE — filters rows before grouping. The predicate is evaluated for each row, and only rows where it is TRUE survive.
• GROUP BY — collapses rows that share the same values in the listed columns into a single group. Combined with aggregate functions, this is how you compute sums, counts, and averages per category.
• HAVING — filters groups after GROUP BY. Aggregate functions are allowed here but not in WHERE.
• ORDER BY — sorts the result. Without it, row order is undefined.
• LIMIT / FETCH FIRST — caps the number of rows returned. LIMIT is non-standard (MySQL, PostgreSQL, SQLite); FETCH FIRST n ROWS ONLY is the SQL:2008 standard, supported by DB2, PostgreSQL, SQL Server 2012+, Oracle 12c, and H2.

A simple example, adapted from the Wikipedia SELECT page:

SELECT title, price, price * 0.06 AS sales_tax
FROM   Book
WHERE  price > 100.00
ORDER BY title;

The expression price * 0.06 AS sales_tax projects a computed column — a value the database evaluates on the fly. The alias sales_tax gives it a name in the result set.

Filtering with WHERE

WHERE is applied row by row before any grouping. The predicate uses three-valued logic: a comparison can be TRUE, FALSE, or UNKNOWN (when either side is NULL). Rows that are not TRUE are dropped, which means NULL values silently disappear from comparisons. To find rows where a column is or is not null, you use IS NULL and IS NOT NULL — = NULL is never true.

A filter can combine conditions with AND, OR, and NOT, and it can use pattern matching (LIKE), set membership (IN), and range checks (BETWEEN). All of these are evaluated by the same engine and typically use an index when one is available; the role of a database index is to let the engine find matching rows without scanning the entire table.

Joining tables

A JOIN clause combines columns from two or more tables into a single result set. It corresponds to a join operation in relational algebra: the engine matches rows from each table according to a predicate, then stitches the matched columns together. The standard join types are:

Using the canonical example from the Wikipedia JOIN page, with employees and departments:

SELECT e.LastName, e.DepartmentID, d.DepartmentName
FROM   employee e
INNER JOIN department d ON e.DepartmentID = d.DepartmentID;

This returns every employee with their department name. Williams, who has a NULL DepartmentID, is excluded because NULL never matches anything (not even NULL itself). The USING shorthand is available when the join columns share a name and you do not need to qualify them:

SELECT * FROM employee INNER JOIN department USING (DepartmentID);

USING is not supported by SQL Server or Sybase. NATURAL JOIN is a stricter version that auto-matches every column with the same name — concise but fragile, because adding a column to either table silently changes the join.

A self-join joins a table to itself, which is the standard way to model hierarchies (employees and their managers, threads and their parent posts) in a flat schema.

Aggregating with GROUP BY

GROUP BY collapses rows that share the same value in the listed columns, then lets you apply aggregate functions — count(*), sum(amount), avg(price), min(date), max(score) — to each group. The classic example, again from the SELECT page, counts authors per book:

SELECT b.title, count(*) AS Authors
FROM   Book b
JOIN   Book_author ba ON b.isbn = ba.isbn
GROUP BY b.title;

The order of evaluation matters: WHERE runs first (row filter), then GROUP BY (collapse), then aggregate functions compute one value per group, then HAVING (group filter), then SELECT (project), then ORDER BY, then LIMIT.

To filter on the result of an aggregate, use HAVING, not WHERE. WHERE count(*) > 5 is a syntax error in standard SQL because the aggregate does not exist yet when WHERE is evaluated. The correct pattern:

SELECT g.GroupName, count(*) AS NumberOfMembers
FROM   users u
INNER JOIN groups g ON g.Userid = u.Userid
GROUP BY GroupName
HAVING count(*) > 5;

The ISO standard evaluation order for a SELECT is, in sequence: evaluate FROM (build a virtual table from the Cartesian product and the ON clause), apply WHERE, apply GROUP BY, apply HAVING, evaluate the SELECT list, apply DISTINCT, then apply ORDER BY. Modern optimizers may reorder operations internally, but the result must match this sequence.

Subqueries and common table expressions

A subquery is a query nested inside another. The most common placement is in the WHERE clause, where it can feed a value to a comparison:

SELECT isbn, title, price
FROM   Book
WHERE  price < (SELECT AVG(price) FROM Book)
ORDER BY title;

A correlated subquery references a column from the outer query and is re-evaluated for each outer row. Correlated subqueries are expressive but often slow; a JOIN with the same intent is usually faster.

A common table expression (CTE) names a subquery so it can be referenced more than once, which is essential for recursive queries:

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM   employees e
  INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

This walks the employee hierarchy starting from id 1 and is a common pattern for organizational trees, threaded comments, and graph data stored in tables.

How a query is executed

When you submit a query, the database parses the SQL, validates it against the schema, and hands the result to a query optimizer. The optimizer's job is to pick a query plan — the actual sequence of operations the engine will run — that produces the correct result as cheaply as possible. The optimizer has two main degrees of freedom:

• Join order. Because joins are commutative and associative, the mathematical result is the same no matter which order the engine joins tables. The cost, however, can differ by orders of magnitude. A bad plan on a 1M-row table joined to a 1M-row table can take hours; the right plan finishes in milliseconds. Optimizers estimate intermediate row counts using table and index statistics, then choose the cheapest plan.
• Join algorithm. Three fundamental algorithms exist: nested loop join (good when one side is small or has an index on the join key), sort-merge join (good when both sides are already sorted on the join key), and hash join (good for large unsorted inputs that fit in memory). Modern databases may also use bitmap join indexes (Teradata, Oracle) for low-cardinality columns.

The actual operations a database performs — sequential scans, index lookups, sorts, hash aggregations, parallel workers — are recorded in an EXPLAIN or EXPLAIN ANALYZE plan, which is the first thing to look at when a query is slow.

Common SQL pitfalls

A few mistakes that catch almost everyone:

• Forgetting that NULL never matches. WHERE DepartmentID = NULL returns zero rows; you need WHERE DepartmentID IS NULL.
• Confusing WHERE and HAVING. WHERE runs before grouping and cannot reference aggregates; HAVING runs after grouping and almost always does.
• Implicit cross joins. Listing several tables in FROM separated by commas without a WHERE clause produces a Cartesian product, which is rarely what you want and can be catastrophically expensive.
• Non-deterministic order without ORDER BY. Without an explicit sort, the database is free to return rows in any order, and that order can change between runs.
• Vendor-specific syntax. LIMIT 10 (MySQL/PostgreSQL/SQLite), FETCH FIRST 10 ROWS ONLY (SQL:2008, DB2/PostgreSQL/SQL Server 2012+/Oracle 12c), TOP 10 (SQL Server, Access), and WHERE ROWNUM <= 10 (Oracle) all do the same thing, but the syntax differs. Writing portable SQL means preferring FETCH FIRST.
• Subtle inner-join NULLs. As the JOIN page notes, inner joins on columns that may contain NULL silently drop rows. For nullable foreign-key columns, a left outer join is usually safer.

FAQ

What is a SQL query?

A SQL query is a declarative statement sent to a relational database that asks for a result set of rows and columns, typically written with the SELECT keyword. The statement describes what data you want, not how to retrieve it; the database engine chooses the execution plan. Queries are read-only and have no persistent effect on the data.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot reference aggregate functions. HAVING filters groups produced by GROUP BY and is the only place aggregates can appear in a filter clause. Use WHERE for column conditions like price > 100, and HAVING for conditions on the result of an aggregate like count(*) > 5.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where the join condition matches in both tables. LEFT OUTER JOIN returns all rows from the left table, with the right table's columns populated when a match exists and NULL when it does not. The rule of thumb: if you might be missing matching rows on the right side but still want every left-side row, use LEFT JOIN. If unmatched rows are bugs, use INNER JOIN.

How does GROUP BY work?

GROUP BY collapses rows that share the same value in the listed columns into a single group, then lets aggregate functions like count(*), sum(amount), and avg(price) compute one value per group. The collapsed rows are no longer individually addressable in the result; only the grouping columns and the aggregate values appear. Filter on the aggregate with HAVING, not WHERE.