SQL Clauses
Introduction
A SQL clause is a meaningful part of a statement that performs a specific role. One clause chooses output columns, another identifies source tables, another combines related rows, and others filter, group, sort, or limit the result. Clauses work together to describe the data operation the database should perform.
SELECT customer_id, total_amount
FROM orders
WHERE order_status = 'PAID'
ORDER BY ordered_at DESC;
This statement contains a SELECT clause, FROM clause, WHERE clause, and ORDER BY clause. Each begins with one or more keywords and includes the expressions or objects needed for that stage.
Understanding clauses is more useful than memorizing isolated keywords. It explains why clauses must appear in a particular written order, why an alias is available in one location but not another, and why moving a predicate between ON and WHERE can change results.
Statement, Clause, Keyword, and Expression
A statement is a complete instruction such as SELECT, INSERT, UPDATE, DELETE, or CREATE TABLE. A clause is a functional section within that statement. A keyword is a reserved word used by SQL grammar, and an expression produces a value.
| Concept | Example | Purpose |
|---|---|---|
| Statement | SELECT ... FROM ... WHERE ... | Complete database instruction |
| Clause | WHERE order_status = 'PAID' | Functional part of the statement |
| Keyword | WHERE | Introduces or structures SQL grammar |
| Expression | quantity * unit_price | Calculates or returns a value |
CASE is an expression construct, not a top-level query clause. Functions and subqueries can also appear inside several different clauses. Recognizing these levels prevents terminology from obscuring how SQL is actually structured.
Core SELECT Statement Clauses
A typical query can use the following written sequence:
SELECT ...
FROM ...
JOIN ... ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ... OFFSET ...;
Not every query needs every clause. A simple query may require only SELECT and FROM. An aggregate report can add grouping and group filtering. Pagination adds a product-supported limiting form after deterministic ordering.
The SELECT Clause
The SELECT clause defines the columns and expressions returned by the query. It can project stored columns, constants, calculations, scalar functions, aggregate functions, window functions, and scalar subqueries.
SELECT
order_id,
quantity * unit_price AS line_total,
UPPER(order_status) AS normalized_status
FROM order_items;
Aliases label expressions in the result. Explicit column lists are generally safer than SELECT * because they define a stable contract, reduce unnecessary data transfer, and remain clear when the table schema changes.
SELECT DISTINCT
DISTINCT removes duplicate result rows based on the complete selected tuple.
SELECT DISTINCT customer_id, order_status
FROM orders;
This returns each distinct customer-and-status combination. It does not independently deduplicate each column, and it does not guarantee ordering. Use ORDER BY when sequence matters.
The FROM Clause
The FROM clause identifies row sources. A source can be a table, view, common table expression, derived table, table-valued function, or another product-supported relation.
SELECT o.order_id, o.total_amount
FROM sales.orders AS o;
Schema qualification such as sales.orders identifies the intended namespace. Aliases make long names concise and are especially valuable when several sources expose columns with the same name.
Multiple Sources and Explicit JOIN
Older SQL often lists several tables separated by commas and places their relationship in WHERE. Explicit JOIN syntax is clearer because it separates relationship predicates from row filters.
SELECT c.customer_name, o.order_id
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id;
The JOIN clause introduces the new source, and the ON clause defines how its rows relate to the sources already available.
INNER JOIN
An inner join retains combinations that satisfy its join condition. Rows without a match are excluded.
SELECT o.order_id, p.payment_id
FROM orders AS o
INNER JOIN payments AS p
ON p.order_id = o.order_id;
The INNER keyword is optional in many databases, but writing it can emphasize the intended join type.
LEFT, RIGHT, and FULL OUTER JOIN
A left join preserves every row from the left source and supplies NULL values when the right side has no match. A right join performs the corresponding operation from the opposite direction, while a full outer join preserves unmatched rows from both sides where supported.
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id;
Left joins are common because query flow remains readable from the primary entity toward optional details. Right and full joins are not supported identically by every database, so portability requirements matter.
CROSS JOIN
A cross join produces every combination of rows from two sources. If one source has 10 rows and the other has 20, the result has 200 combinations before later filtering.
SELECT s.size_name, c.color_name
FROM product_sizes AS s
CROSS JOIN product_colors AS c;
This is useful for generating intended combinations, calendars, or test matrices. An accidental cross join can produce an enormous result, so use explicit syntax and review row counts.
The ON Clause
The ON clause states the relationship condition for a join. It may include more than one comparison:
SELECT i.invoice_id, p.payment_id
FROM invoices AS i
JOIN payments AS p
ON p.invoice_id = i.invoice_id
AND p.currency_code = i.currency_code;
JOIN chooses the source and join type; ON defines matching. Missing or incorrect conditions can create duplicate matches, omit valid rows, or produce a Cartesian result.
ON Versus WHERE
For an inner join, moving a predicate between ON and WHERE can sometimes produce the same rows. With an outer join, placement is often semantically different.
-- Preserve all customers, but attach only paid orders.
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.order_status = 'PAID';
If o.order_status = 'PAID' is moved to WHERE, rows with no matching order have a NULL status and are removed. The left join then behaves like an inner join for that condition. Put relationship and optional-side qualification in ON; use WHERE for final row filtering according to the intended result.
The WHERE Clause
The WHERE clause filters individual rows before grouping. It accepts a Boolean search condition built from comparisons, logical operators, ranges, lists, patterns, null checks, and subqueries.
SELECT order_id, total_amount
FROM orders
WHERE order_status = 'PAID'
AND total_amount >= 100
AND ordered_at >= :start_time;
Rows for which the condition is true are retained. Conditions that evaluate to false or unknown are rejected. The unknown case is why IS NULL must be used instead of = NULL.
AND, OR, NOT, and Parentheses
Logical operators combine predicates. AND commonly has higher precedence than OR, but explicit parentheses are safer when business logic mixes them.
WHERE is_active = TRUE
AND (customer_type = 'BUSINESS'
OR lifetime_value >= 10000)
Without parentheses, a reader may interpret the condition differently from the parser. Format one major condition per line and group related alternatives visibly.
IN, BETWEEN, LIKE, and NULL
WHERE order_status IN ('PAID', 'SHIPPED')
AND ordered_at BETWEEN :start_time AND :end_time
AND customer_name LIKE 'A%'
AND deleted_at IS NULL
IN tests membership. BETWEEN is inclusive at both boundaries, which requires care with timestamps. LIKE performs pattern matching with product- and collation-dependent case behavior. IS NULL tests missing values according to SQL's null semantics.
EXISTS
EXISTS tests whether a subquery returns at least one row. It is effective for expressing existence without multiplying outer rows.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.order_status = 'PAID'
);
The inner query is correlated through customer_id. The selected constant is conventional because only row existence matters.
The GROUP BY Clause
GROUP BY forms groups of rows that share one or more values. Aggregate functions then summarize each group.
SELECT customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;
Every selected expression generally must be aggregated, grouped, or otherwise allowed by the database's functional-dependency rules. Selecting an unrelated detail column from many rows in a group is ambiguous.
Grouping by Multiple Columns
SELECT customer_id,
order_status,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, order_status;
The result grain is one row per customer-and-status combination. Understanding grain is essential: each additional grouping column can produce more result groups.
The HAVING Clause
HAVING filters groups after aggregation. It is the appropriate place for conditions based on aggregate results.
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE order_status = 'PAID'
GROUP BY customer_id
HAVING SUM(total_amount) >= 1000;
The WHERE condition removes unpaid rows before groups are built. HAVING then keeps only customer groups whose paid total reaches the threshold.
WHERE Versus HAVING
| Feature | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups or aggregate result |
| Logical timing | Before grouping | After grouping |
| Typical condition | order_status = 'PAID' | SUM(total_amount) > 1000 |
| Performance principle | Reduce rows early when logically valid | Apply true group conditions |
Some databases allow HAVING without an explicit GROUP BY, treating the input as one group. That feature should be used only when its intent is clear and portability has been considered.
The ORDER BY Clause
ORDER BY defines result ordering. Each expression can be ascending or descending, and later expressions break ties from earlier ones.
SELECT order_id, ordered_at, total_amount
FROM orders
ORDER BY ordered_at DESC, order_id DESC;
The stable order_id tie-breaker makes ordering deterministic when two orders share a timestamp. Without ORDER BY, row sequence is not guaranteed even if results appeared stable during testing.
Ordering by Alias or Position
Many databases permit a selected alias in ORDER BY:
SELECT quantity * unit_price AS line_total
FROM order_items
ORDER BY line_total DESC;
Ordering by column position, such as ORDER BY 1, may also be supported but is fragile because changing the select list can silently change sort meaning. Descriptive aliases are easier to maintain.
NULL Ordering
The default position of NULL values differs among databases and sort directions. Some products support explicit NULLS FIRST or NULLS LAST; others require an expression.
ORDER BY completed_at NULLS LAST, order_id
If null placement matters, state it explicitly using supported syntax rather than relying on a vendor default.
LIMIT, OFFSET, TOP, and FETCH
Databases provide different ways to restrict result count. PostgreSQL, MySQL, and SQLite commonly use LIMIT; SQL Server supports TOP and OFFSET ... FETCH; standard-style FETCH FIRST is available in several products.
SELECT order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC, order_id DESC
LIMIT 25 OFFSET 50;
The exact syntax is vendor-specific. A limit without deterministic ordering returns some qualifying rows, not a guaranteed business-defined subset.
Deterministic Pagination
Offset pagination should use a complete, stable order. Otherwise, tied values can move among pages, and concurrent inserts may shift row positions.
SELECT order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC, order_id DESC
FETCH FIRST 25 ROWS ONLY;
For large or frequently changing data, keyset pagination can continue after the last ordered key instead of scanning an ever-growing offset. Its predicates and syntax depend on the ordering columns and database.
Written Clause Order
SQL grammar requires clauses in a defined written order:
SELECT
FROM
JOIN ... ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT / OFFSET / FETCH
WHERE cannot appear after ORDER BY, and HAVING cannot be written before GROUP BY. Although optimizers can transform execution, the source still must follow grammar.
Logical Query Processing Order
A useful conceptual order for understanding name visibility and row flow is:
FROMand source constructionJOINandONWHEREGROUP BYHAVINGSELECTDISTINCTORDER BY- Limiting and pagination
This is a semantic model, not a promise of physical execution. The optimizer may reorder joins, push predicates, or use indexes while preserving the query's meaning.
Why SELECT Aliases Usually Fail in WHERE
A select-list alias is conceptually created after WHERE has filtered rows, so it is normally unavailable there:
-- Usually invalid because line_total is not visible in WHERE.
SELECT quantity * unit_price AS line_total
FROM order_items
WHERE line_total > 100;
Repeat the expression or create it in a subquery or CTE:
WITH priced_items AS (
SELECT quantity * unit_price AS line_total
FROM order_items
)
SELECT line_total
FROM priced_items
WHERE line_total > 100;
The alias often works in ORDER BY because ordering is logically later.
The WITH Clause and CTEs
The WITH clause defines common table expressions used by the following statement. CTEs can clarify stages, support recursion where available, and avoid repeating a subquery.
WITH paid_orders AS (
SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_status = 'PAID'
), customer_totals AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM paid_orders
GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent >= 1000;
Each CTE is its own query block with clauses and scope. CTE optimization behavior varies by database and version, so do not assume it always materializes or always inlines.
Subqueries Inside Clauses
A subquery can appear in several contexts. A predicate subquery can support EXISTS or membership testing, a derived table can appear in FROM, and a scalar subquery can return one value in SELECT.
SELECT c.customer_id,
(SELECT COUNT(*)
FROM orders AS o
WHERE o.customer_id = c.customer_id) AS order_count
FROM customers AS c;
Correlated scalar subqueries can be convenient but may execute inefficiently depending on the optimizer and data. Compare them with joins or pre-aggregation when performance matters.
Expressions and CASE Inside Clauses
Expressions can appear in the select list, predicates, grouping, ordering, and other product-supported positions. CASE returns a value and can classify rows:
SELECT order_id,
CASE
WHEN total_amount >= 1000 THEN 'HIGH'
WHEN total_amount >= 100 THEN 'MEDIUM'
ELSE 'LOW'
END AS order_band
FROM orders;
The expression belongs to the SELECT clause. Calling CASE a clause can obscure where it is valid and how its result is scoped.
Window Clauses Versus GROUP BY
Window functions calculate across related rows without collapsing them into one row per group. Their OVER specification can include PARTITION BY and an internal ORDER BY.
SELECT order_id,
customer_id,
total_amount,
SUM(total_amount) OVER (
PARTITION BY customer_id
) AS customer_total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at DESC, order_id DESC
) AS recency_rank
FROM orders;
PARTITION BY defines window partitions but does not reduce row count. The order inside OVER controls the window calculation; a final query ORDER BY controls presentation.
QUALIFY and Other Vendor-Specific Clauses
Some databases provide QUALIFY to filter after window-function evaluation. Where unavailable, place the window calculation in a subquery or CTE and filter it in the outer query.
Other vendor-specific clauses include data-modification result features such as RETURNING or SQL Server's OUTPUT, and locking clauses such as FOR UPDATE. Availability and exact semantics differ, so portable SQL should isolate such features and document alternatives.
INSERT INTO and VALUES
An insert statement identifies the target and supplies rows through VALUES or a query:
INSERT INTO customers (customer_id, customer_name, is_active)
VALUES
(101, 'Asha', TRUE),
(102, 'David', TRUE);
Explicit target columns protect the statement from column-order changes and make the mapping reviewable.
INSERT SELECT
INSERT INTO archived_orders (
order_id, customer_id, total_amount, ordered_at
)
SELECT order_id, customer_id, total_amount, ordered_at
FROM orders
WHERE ordered_at < :archive_cutoff;
The query clauses decide which rows and values are inserted. Validate row counts and type compatibility before moving large or irreversible data sets.
UPDATE and SET
The SET clause assigns new values, while WHERE defines the affected rows.
UPDATE orders
SET order_status = 'ARCHIVED',
updated_at = CURRENT_TIMESTAMP
WHERE order_status = 'COMPLETED'
AND completed_at < :archive_cutoff;
An update without WHERE affects every row unless another statement feature limits it. Preview the exact predicate with SELECT, use an appropriate transaction, and verify affected counts.
DELETE and Safety
DELETE FROM temporary_import_rows
WHERE import_batch_id = :batch_id
AND validation_status = 'REJECTED';
A delete without WHERE removes every row that the statement targets. Comments do not provide safety. Use least privilege, backups, transactions where supported, bounded batches, review, and post-operation validation.
CREATE TABLE Components
Data-definition statements contain structured components that are sometimes informally called clauses. A table definition includes columns, data types, nullability, defaults, and constraints.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
total_amount DECIMAL(12, 2) NOT NULL,
order_status VARCHAR(20) NOT NULL DEFAULT 'NEW',
CONSTRAINT ck_orders_total_nonnegative
CHECK (total_amount >= 0)
);
DEFAULT supplies a value when one is not provided, while CHECK enforces a row-level condition. Product terminology and supported constraint syntax vary.
Clause Scope and Query Blocks
Each query block has its own sources, aliases, filters, groups, select list, and ordering. An alias defined inside a derived table or CTE is not automatically visible outside it unless projected.
SELECT summary.customer_id, summary.total_spent
FROM (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
) AS summary
WHERE summary.total_spent >= 1000;
Understanding scope prevents invalid references and clarifies where filtering should occur.
Complex Query Example
WITH paid_orders AS (
SELECT order_id, customer_id, total_amount, ordered_at
FROM orders
WHERE order_status = 'PAID'
AND ordered_at >= :start_time
)
SELECT
c.customer_id,
c.customer_name,
COUNT(po.order_id) AS order_count,
SUM(po.total_amount) AS total_spent
FROM customers AS c
JOIN paid_orders AS po
ON po.customer_id = c.customer_id
WHERE c.is_active = TRUE
GROUP BY c.customer_id, c.customer_name
HAVING SUM(po.total_amount) >= :minimum_total
ORDER BY total_spent DESC, c.customer_id
FETCH FIRST 50 ROWS ONLY;
The CTE defines eligible paid orders. FROM and JOIN build the source, WHERE removes inactive customers, GROUP BY establishes one group per customer, HAVING removes low-value groups, SELECT calculates output, and ORDER BY plus FETCH returns a deterministic top subset.
Commonly Confused Clause Pairs
SELECT and FROM answer different questions. FROM establishes which rows are available, while SELECT defines which values are projected from those rows. A query can calculate an expression that never existed as a stored column because projection occurs after the row source is established.
GROUP BY and ORDER BY are also unrelated despite both accepting column lists. Grouping changes result grain by combining rows for aggregate calculation. Ordering changes only the sequence in which completed result rows are returned. A grouped query still needs ORDER BY when presentation order matters.
DISTINCT and GROUP BY can sometimes produce the same set of unique values, but they communicate different intent. DISTINCT removes duplicate projected rows. GROUP BY forms groups, usually so aggregates can summarize them. Prefer the construct that expresses the requirement directly instead of using grouping only as an unexplained deduplication technique.
Finally, WHERE and ON both contain predicates but operate in different semantic locations. ON defines source matching; WHERE filters the constructed rows. The distinction is especially important with outer joins because it determines whether unmatched rows survive.
Building Queries Incrementally
Start with the required output and source. Add joins one at a time and verify row counts. Add row filters, then introduce aggregation and verify grain. Add group filters only after aggregates are correct. Finally, apply deterministic ordering and pagination.
-- 1. Confirm source rows.
SELECT * FROM orders;
-- 2. Add row eligibility.
SELECT * FROM orders WHERE order_status = 'PAID';
-- 3. Add grouping.
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE order_status = 'PAID'
GROUP BY customer_id;
This approach isolates errors and makes unexpected duplication visible before the query becomes complex.
Clause Formatting
Place major clauses on separate lines, indent selected expressions and join predicates consistently, and align related conditions. Formatting should reveal structure rather than compress it.
SELECT
c.customer_id,
c.customer_name,
SUM(o.total_amount) AS total_spent
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_status = 'PAID'
GROUP BY
c.customer_id,
c.customer_name
ORDER BY
total_spent DESC,
c.customer_id;
Clear layout makes missing join conditions, misplaced filters, and grouping errors easier to identify during review.
Common Clause Errors
Frequent errors include writing clauses in the wrong order, placing WHERE after GROUP BY or ORDER BY, filtering aggregates in WHERE, omitting an ON condition, selecting non-grouped detail columns, and assuming GROUP BY or DISTINCT sorts output.
Other mistakes are limiting before defining deterministic order, using a select alias in WHERE, and moving an optional-side predicate from ON to WHERE in a left join. These are semantic errors even when the parser accepts the statement.
Clauses and Performance
Clauses describe intent; the optimizer chooses a physical plan. Logically valid row predicates should usually be expressed early enough for the optimizer to reduce data, but performance depends on indexes, statistics, cardinality, join strategy, data distribution, and product behavior.
Do not rewrite a clear query merely to imitate logical clause order at the physical level. Inspect execution plans and measurements. A well-placed predicate can still be slow without a useful access path, and a CTE or subquery can be optimized differently across systems.
Clause Categories
| Category | Representative clauses | Role |
|---|---|---|
| Projection | SELECT | Define returned expressions |
| Source | FROM, JOIN, ON | Build the row source |
| Row filtering | WHERE | Remove individual rows |
| Aggregation | GROUP BY, HAVING | Form and filter groups |
| Presentation | ORDER BY | Define result sequence |
| Pagination | LIMIT, OFFSET, FETCH, TOP | Restrict output rows |
| Data modification | VALUES, SET, RETURNING | Supply or expose changed values |
Practical Review Checklist
- Confirm every clause appears in valid written order.
- Use explicit column lists and meaningful aliases.
- Qualify ambiguous columns in multi-source queries.
- Verify every join condition and expected relationship cardinality.
- Keep outer-join predicates in the clause that preserves intended unmatched rows.
- Use
WHEREfor row conditions andHAVINGfor true group conditions. - Confirm selected non-aggregate expressions match the grouping grain.
- Never assume
GROUP BY,DISTINCT, or storage order sorts results. - Add stable tie-breakers before limiting or paginating.
- Review null behavior, operator precedence, and clause scope.
- Preview and protect every
UPDATEorDELETEpredicate. - Measure plans instead of assuming logical order equals physical execution.
Interview Questions and Answers
What is an SQL clause? It is a functional part of a statement that performs a role such as selecting output, identifying sources, filtering rows, grouping data, or sorting results.
What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters groups after aggregation.
Why can a SELECT alias usually be used in ORDER BY but not WHERE? In the logical processing model, WHERE is evaluated before the select list creates the alias, while ORDER BY is later.
Does GROUP BY sort results? No. Only an explicit ORDER BY guarantees presentation order.
What is the difference between JOIN and ON? JOIN identifies the source and join type; ON defines matching conditions.
Why does predicate placement matter in a LEFT JOIN? A condition in ON can limit right-side matches while preserving left rows, whereas the same condition in WHERE can remove unmatched rows.
What is logical query processing order? It is a conceptual sequence used to understand row flow and name visibility; it is not necessarily the physical execution plan.
Why must pagination include ORDER BY? Without deterministic ordering, the database is free to return any qualifying subset, and pages can be unstable.
Key Takeaway
SQL clauses divide a statement into understandable responsibilities. FROM and joins build sources, WHERE filters rows, GROUP BY creates groups, HAVING filters those groups, SELECT defines output, and ORDER BY controls presentation before a limiting form returns the final subset.
QUERY MODEL =
SOURCE + RELATIONSHIPS + ROW FILTERS + GROUPS + OUTPUT + ORDER + LIMIT
Write clauses in valid grammar order, reason about their logical order, and verify scope and row grain at each stage. That mental model makes complex queries easier to build, debug, optimize, and explain.