SQL Keywords Reference

SQL keywords are the reserved words that give a SQL statement its structure and meaning — words like SELECT, WHERE, JOIN, and GROUP BY that the database engine recognizes as instructions rather than as literal data. This reference lesson organizes the most important SQL keywords into logical categories, explains what each one actually does, and shows how they combine into real, runnable queries. Once you understand the categories — instead of memorizing a flat alphabetical list — you can read unfamiliar SQL with confidence and reason about what any new keyword you meet is probably for.

Overview: How SQL Keywords Work

Every SQL statement is built from four kinds of tokens: keywords (reserved instruction words), identifiers (the table and column names you chose), literals (actual values like 'Engineering' or 42), and operators (=, >, AND, and so on). When the database engine parses a statement, it tokenizes the text and checks each token against the grammar rules for the clause it appears in. This is why keywords are "reserved" in most systems: trying to use SELECT or ORDER as a plain column name will either fail outright or force you to quote it as a delimited identifier.

SQL keywords fall into five broad functional categories:

• DDL — Data Definition Language: CREATE, ALTER, DROP, TRUNCATE. These define or change the shape of the database itself: tables, indexes, views, and constraints.
• DML — Data Manipulation Language: INSERT, UPDATE, DELETE. These change the data stored inside tables.
• DQL — Data Query Language: SELECT, together with FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, and LIMIT. These retrieve data without changing it.
• DCL — Data Control Language: GRANT, REVOKE. These manage who is allowed to do what to a database object.
• TCL — Transaction Control Language: COMMIT, ROLLBACK, SAVEPOINT. These control whether a group of statements is applied together or undone together.

Beyond these five families, a large set of clause and operator keywords modifies DQL and DML statements: logical operators (AND, OR, NOT), comparison and set operators (IN, BETWEEN, LIKE, IS NULL, EXISTS), join keywords (INNER JOIN, LEFT JOIN, ON), and result modifiers (DISTINCT, AS, LIMIT, OFFSET, UNION).

Case Sensitivity and Style

Keywords are case-insensitive in virtually every SQL engine, so select, Select, and SELECT parse identically. Writing keywords in UPPERCASE and identifiers in lowercase is a style convention, not a requirement — but it makes a multi-clause query far easier to scan at a glance.

Reserved vs. Non-Reserved Keywords

Some keywords are strictly reserved and can never be used as an identifier (SELECT, FROM, WHERE), while others are only weakly reserved and vary by engine (DATE, YEAR, and ROLE are usable as column names in some systems). If you must use a reserved word as a name, wrap it in a delimited identifier: double quotes in standard SQL and SQLite, backticks in MySQL, and square brackets in SQL Server.

Syntax

Most day-to-day SQL is a SELECT statement, and its keywords always appear in this written order (square brackets mark optional clauses):

SELECT [DISTINCT] column_list
FROM table_name
[JOIN other_table ON join_condition]
[WHERE row_condition]
[GROUP BY grouping_columns]
[HAVING group_condition]
[ORDER BY sort_columns [ASC|DESC]]
[LIMIT row_count [OFFSET skip_count]];

Keyword	Category	Purpose
SELECT	DQL	Choose which columns or expressions to return
FROM	DQL	Specify the source table(s)
WHERE	DQL	Filter individual rows before any grouping
GROUP BY	DQL	Collapse rows into groups for aggregation
HAVING	DQL	Filter groups after aggregation
ORDER BY	DQL	Sort the final result set
LIMIT / OFFSET	DQL	Restrict how many rows come back
JOIN / ON	DQL	Combine rows from two or more tables
INSERT INTO	DML	Add new rows to a table
UPDATE / SET	DML	Modify existing rows
DELETE FROM	DML	Remove rows
CREATE TABLE	DDL	Define a new table and its columns
ALTER TABLE	DDL	Change an existing table’s structure
DROP TABLE	DDL	Delete a table and all its data permanently
GRANT / REVOKE	DCL	Give or remove user permissions
COMMIT / ROLLBACK	TCL	Finalize or undo a transaction

Note that this template is not itself valid SQL — column_list and table_name are placeholders you replace with real names, and the square brackets are notation meaning "optional," not literal SQL syntax.

Examples

Example 1: Filtering and Sorting (WHERE, ORDER BY)

This example builds a small students table and uses WHERE to keep only 10th graders, then ORDER BY to rank them by GPA:

SELECT name, grade_level, gpa
FROM students
WHERE grade_level = 10
ORDER BY gpa DESC;

Result:

name	grade_level	gpa
Elin	10	3.5
Ben	10	3.2

The WHERE keyword removes the 9th- and 11th-grade rows before anything else happens, and ORDER BY ... DESC then sorts the two surviving rows from the highest GPA to the lowest.

Example 2: Combining Tables (JOIN, ON)

This example creates authors and books tables and uses JOIN ... ON to combine them, plus WHERE and ORDER BY:

SELECT b.title, a.author_name, b.year
FROM books b
JOIN authors a ON b.author_id = a.author_id
WHERE b.year > 1814
ORDER BY b.year;

Result:

title	author_name	year
Emma	Jane Austen	1815
The Adventures of Tom Sawyer	Mark Twain	1876

The JOIN ... ON keywords match each book to its author using the shared author_id column. Pride and Prejudice (1813) is excluded because it fails the WHERE b.year > 1814 condition.

Example 3: Grouping and Aggregating (GROUP BY, HAVING)

This example builds a sales table and uses GROUP BY, aggregate functions, and HAVING together:

SELECT region,
       COUNT(*) AS num_sales,
       SUM(amount) AS total_amount,
       AVG(amount) AS avg_amount
FROM sales
GROUP BY region
HAVING COUNT(*) >= 2
ORDER BY total_amount DESC;

Result:

region	num_sales	total_amount	avg_amount
South	2	600	300.0
North	3	550	183.33

GROUP BY region collapses the six sales rows into three region groups (North, South, East). HAVING COUNT(*) >= 2 then drops the East group, which only has one sale, before the remaining groups are sorted by total_amount.

How It Works Step by Step: Logical Query Processing Order

SQL keywords are written in one order but executed in a different, fixed logical order. Understanding this order explains rules that otherwise seem arbitrary — like why WHERE can’t reference a column alias defined in SELECT, and why aggregate functions belong in HAVING, not WHERE. Using Example 3 above, the engine conceptually proceeds as:

1. FROM / JOIN — identify the source rows: all six rows of sales.
2. WHERE — filter individual rows. (Not used in Example 3, but this step runs before any grouping.)
3. GROUP BY — collapse the surviving rows into groups: North, South, East.
4. HAVING — filter whole groups using aggregate conditions: COUNT(*) >= 2 removes East.
5. SELECT — compute the requested columns and aggregate expressions for each remaining group.
6. ORDER BY — sort the final rows by total_amount.
7. LIMIT — (not used here) would trim the sorted result to a fixed number of rows, applied last.

Because WHERE runs before GROUP BY, it can only see raw, ungrouped rows — it has no concept of a group total yet, which is exactly why aggregate functions like COUNT() or SUM() are not allowed inside a WHERE clause. By the time HAVING runs, groups already exist, so aggregate functions are exactly what belongs there.

Common Mistakes

Mistake 1: Aggregate Functions Inside WHERE

It’s tempting to filter on a count the same way you’d filter on a column, but the query planner rejects this because WHERE executes before grouping has happened:

SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 1
GROUP BY department;

This raises an error (a "misuse of aggregate function" failure) because COUNT(*) has no meaning yet at the point WHERE is evaluated. The fix is to move the condition into HAVING, which runs after GROUP BY:

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

This version executes cleanly and returns whichever department(s) among the seeded employees have more than one row.

Mistake 2: Ambiguous Column References in a JOIN

When two joined tables share a column name, referencing that name without a table qualifier is ambiguous to the parser, even though it seems obvious to a human reader:

SELECT customer_id, order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

Both customers and orders have a customer_id column, so this raises an "ambiguous column name" error. Qualify the column with its table name (or alias) to fix it:

SELECT customers.customer_id, customers.customer_name, orders.order_date, orders.amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

This returns one row per order, each carrying the matching customer’s id and name alongside that order’s date and amount, since every seeded order references an existing customer.

Best Practices

• Write keywords in UPPERCASE and identifiers in lowercase (or snake_case) so a query’s structure is visible at a glance.
• Put filtering logic in WHERE whenever it doesn’t depend on an aggregate, and reserve HAVING strictly for conditions on aggregated groups — it’s typically faster because it discards rows before the (more expensive) grouping step.
• Always qualify column names with a table name or alias once a query involves more than one table, even when a column isn’t currently ambiguous — a later schema change can silently make it so.
• Avoid using reserved keywords as table or column names; if you must, quote them consistently and prefer renaming instead.
• Use explicit JOIN ... ON syntax rather than comma-separated tables with a join condition in WHERE — it keeps join logic and filter logic visually separate.
• Remember that WHERE runs before SELECT, so you generally cannot reference a SELECT-defined column alias inside WHERE (engines vary on GROUP BY/ORDER BY alias support, so check yours).
• Always pair DELETE FROM or UPDATE with a WHERE clause unless you genuinely intend to touch every row in the table.

Practice Exercises

1. Using the shared employees table (columns: id, name, department, salary, hire_date), write a query that returns each department’s average salary, but only for departments with more than one employee. Which keyword do you need in addition to SELECT, FROM, and GROUP BY?
2. Using the shared customers and orders tables, write a query with an explicit JOIN ... ON that lists each customer’s name next to their order amount, qualifying every column that exists in both tables.
3. The following query fails to execute: SELECT category, MAX(price) FROM products WHERE MAX(price) > 100 GROUP BY category; Identify which keyword is misused, and rewrite the query so it runs correctly.

Summary

• SQL keywords fall into five functional categories: DDL (structure), DML (data changes), DQL (queries), DCL (permissions), and TCL (transactions).
• Keywords are case-insensitive everywhere, but UPPERCASE keywords with lowercase identifiers is the near-universal convention.
• A query’s written clause order (SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT) differs from its logical execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT).
• WHERE filters individual rows before grouping; HAVING filters groups after aggregation — aggregate functions belong only in the latter.
• Unqualified column names that exist in more than one joined table cause an ambiguous-column error; always qualify them.
• Reserved keywords cannot be used as identifiers unless quoted as delimited identifiers, and the quoting syntax differs by database engine.