SQL Syntax Rules

SQL looks like English, but it has strict rules. If you know them, most beginner errors disappear.

Rule 1: clauses come in a fixed order

For a SELECT, the clauses must appear in this order:

1
2
3
4
5
6
7

SELECT   what to show
FROM     which table
WHERE    which rows
GROUP BY how to group
HAVING   which groups
ORDER BY how to sort
LIMIT    how many

You can leave clauses out, but you cannot reorder them.

1

SELECT name FROM (SELECT 'Asha' AS name) AS t LIMIT 1 WHERE name = 'Asha';

1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE name = 'Asha''

The error points near WHERE, because WHERE cannot come after LIMIT. MySQL shows the text from where it got lost.

Rule 2: commas separate items, never end a list

1

SELECT 1 AS a, 2 AS b, 3 AS c;

1
2
3
4
5
6

+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 row in set

A comma after the last item (3 AS c,) is a syntax error. This is the most common typing mistake when you edit a list of columns.

Rule 3: parentheses group things

Parentheses are used for lists of columns and values, sub-queries, function arguments and to control calculation order:

1

SELECT (2 + 3) * 4 AS with_brackets, 2 + 3 * 4 AS without_brackets;

1
2
3
4
5
6

+---------------+------------------+
| with_brackets | without_brackets |
+---------------+------------------+
|            20 |               14 |
+---------------+------------------+
1 row in set

Every opening bracket needs a closing one. Many editors highlight the matching pair.

Rule 4: text goes in quotes, names do not

• Text values use single quotes: 'Pune'.
• Table and column names are written as they are, or in backticks if they need protection (see Identifiers and Backticks).

1

SELECT 'name' AS text_value, 5 AS number_value;

1
2
3
4
5
6

+------------+--------------+
| text_value | number_value |
+------------+--------------+
| name       |            5 |
+------------+--------------+
1 row in set

Rule 5: whitespace is free

Spaces, tabs and new lines are ignored between words. A single statement can run over twenty lines. You may not split a word in half or put a space inside a name.

Rule 6: the order of execution is not the order of writing

You write SELECT first, but MySQL logically starts with FROM, then WHERE, GROUP BY, HAVING, then SELECT, then ORDER BY and LIMIT. That is why you cannot use a column alias from SELECT inside WHERE:

1
2
3

CREATE TABLE items (id INT, price INT);
INSERT INTO items VALUES (1, 50), (2, 150);
SELECT id, price * 2 AS double_price FROM items WHERE double_price > 100;

1

ERROR 1054 (42S22): Unknown column 'double_price' in 'where clause'

The alias double_price does not exist yet when WHERE runs. Repeat the expression in WHERE, or use a sub-query:

1

SELECT id, price * 2 AS double_price FROM items WHERE price * 2 > 100;

1
2
3
4
5
6

+------+--------------+
| id   | double_price |
+------+--------------+
|    2 |          300 |
+------+--------------+
1 row in set