SQL Syntax Explained with Core Commands and Examples
For SQL beginners who need the syntax of every command family in one place; no prior SQL experience is assumed, and each example runs on its own.
On this page
A statement that reads perfectly well to you can still come back rejected, because SQL cares about the order of its clauses and not at all about how you break the lines. Every command opens with a keyword that names the action, and the clauses after it come in a fixed order for that command. Put WHERE after ORDER BY and the engine stops on it, whichever engine you are running.
How a SQL statement is structured
A query is the clearest example of the fixed order. SELECT names the columns, FROM names the table, and WHERE, GROUP BY, HAVING and ORDER BY are optional but have to appear in that sequence when they do appear.
SELECT column_list
FROM table_name
WHERE row_condition
GROUP BY grouping_columns
HAVING group_condition
ORDER BY sort_columns;
INSERT, UPDATE, DELETE and the commands that define tables each have their own clause order rather than sharing this one, and within any single command the order is not negotiable. Keywords are case-insensitive[1]: SELECT, select and Select all run identically. The convention on this page is uppercase keywords with lowercase table and column names, purely so the two are easy to tell apart. Whitespace and line breaks only separate one word from the next, so the query above runs the same written on a single line.
A semicolon ends a statement. Most interactive tools run a single statement without one, but a semicolon is what separates two statements sent together, and standard SQL asks for it. Ending every statement with a semicolon, including the last one in a script, is the safer habit.
SQL command families
SQL commands split into five families by what they act on: DDL defines structure, DML changes data, DQL reads it, DCL controls access, and TCL controls how changes become permanent. Every example below runs against the same two-table schema, created by the first command of the first family, so each command can be read against the tables the previous ones left behind.
Data definition language (DDL)
DDL commands create, change and remove the structures that hold data: tables, columns and constraints. CREATE TABLE defines a new table, including a foreign key that ties it to another one.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
ALTER TABLE changes a table that already exists: adding a column, or, as below, adding a constraint that was left off the original CREATE TABLE. Declaring the foreign key this way instead of inline gives the same relationship, added after the fact.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (customer_id);
DROP TABLE removes a table and every row it holds. Most engines accept IF EXISTS so the statement does not error when the table is already gone, and the order matters here: orders references customers, so orders has to go first.
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
Data manipulation language (DML)
DML commands add, change and remove rows without touching the table structure itself. INSERT INTO adds a new row, naming the columns being set and a matching value for each.
INSERT INTO customers (customer_id, name, email)
VALUES (1, 'Jane Doe', '[email protected]');
INSERT INTO orders (order_id, customer_id, total)
VALUES (1001, 1, 84.50);
UPDATE changes existing rows, and DELETE removes them. Both need a WHERE clause naming which rows are affected; leave it off and every row in the table is updated or deleted.
UPDATE customers
SET email = '[email protected]'
WHERE customer_id = 1;
DELETE FROM orders
WHERE order_id = 1001;
Data query language (DQL)
DQL is SELECT: it reads rows without changing anything. A JOIN reads across the foreign key declared earlier, returning a customer's name next to the total of an order they placed.
SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.total > 50;
| name | order_id | total |
|---|---|---|
| Jane Doe | 1001 | 84.50 |
One row comes back, because one order exists and its total clears the condition in the WHERE clause.
Data control language (DCL)
DCL commands grant and remove a database user's access. GRANT gives a named privilege on a table; REVOKE takes it back.
GRANT SELECT, INSERT ON orders TO reporting_user;
REVOKE INSERT ON orders FROM reporting_user;
Transaction control language (TCL)
TCL wraps one or more DML statements so that either all of them take effect or none do. BEGIN opens the block[2], COMMIT makes every change inside it permanent[3], and ROLLBACK discards them instead, which is what you want when a later statement in the block fails and the earlier ones should not be kept.
BEGIN;
UPDATE customers SET email = '[email protected]' WHERE customer_id = 1;
INSERT INTO orders (order_id, customer_id, total) VALUES (1002, 1, 19.99);
COMMIT;
Where SQL syntax differs between database engines
Everything above runs on PostgreSQL, MySQL and SQL Server without changes. Four things do not carry over as written, and reading the target engine's own documentation before shipping DDL or a script is the only reliable way to catch them.
Auto-incrementing a primary key is the first. PostgreSQL uses GENERATED ALWAYS AS IDENTITY, or the older SERIAL shorthand, while MySQL marks the column with the AUTO_INCREMENT attribute[4].
Limiting the rows returned is the second. PostgreSQL and MySQL both take LIMIT after ORDER BY, SQL Server puts TOP (n) directly after SELECT[5], and standard SQL and Oracle use FETCH FIRST n ROWS ONLY. That one clause alone has four spellings across the engines that use it, and a dedicated page walks through them side by side.
Quoting an identifier is the third, and it comes up as soon as a name collides with a keyword or has to keep its mixed case. PostgreSQL and standard SQL use double quotes, MySQL uses backticks, SQL Server uses square brackets.
String concatenation is the fourth. PostgreSQL treats the double pipe as the concatenation operator, matching the SQL standard. MySQL's default sql_mode does not[6]: there the double pipe is a synonym for the OR operator, and CONCAT() is what joins strings.
Divergence is not limited to relational engines either. Cassandra's CQL looks like SQL on the surface, but it has no foreign keys and can never alter which columns form the primary key, only rename one of them.
Reading common SQL syntax errors
A malformed statement stops the engine at the point where it can no longer make sense of the input, and the position it reports is usually just past the actual mistake rather than on top of it. A misspelled keyword shows this plainly: writing FORM instead of FROM points the error at the table name that follows, because up to FORM the statement still looked like it could go somewhere.
ERROR: syntax error at or near "customers"
LINE 1: SELECT * FORM customers;
^
Three more patterns are worth recognizing on sight. A missing comma between two column names is read as one invalid identifier. A string left open, because a quote inside it was not escaped, swallows everything after it until the next quote. A clause out of order, such as WHERE placed after ORDER BY, breaks the fixed order every statement follows. None of these are engine-specific, and only the wording of the message changes from one engine to the next.
The commands above are the ones DbSchema's SQL Editor runs against a connected database, and Ctrl+Space there opens its Auto-Complete popup, which suggests the table names, column names and keywords of the schema you are connected to. Download DbSchema at https://dbschema.com/download.html and try the statements above against real tables: connecting, reverse-engineering the schema into a diagram and the SQL Editor are all in the free Community Edition.
Frequently asked questions
Do I need a semicolon at the end of every query?
Not for a single statement run on its own, which most SQL editors execute without one. A semicolon is required to separate two or more statements sent together, and writing one after every statement, including the last, means the script does not depend on which program runs it.
Is SQL syntax case-sensitive?
Keywords are not: SELECT and select behave identically. Whether table and column names are depends on the engine and on how they were created, and PostgreSQL folds an unquoted name to lower case[1]. Whether two string values count as equal is decided by the collation of the comparison, which is an engine and column setting rather than a property of SQL.
What is the difference between DDL and DML?
DDL changes what a table looks like: its columns, its constraints, its existence. DML changes the rows inside a table that already exists. CREATE TABLE and ALTER TABLE are DDL, INSERT, UPDATE and DELETE are DML.
Sources
Create ER diagrams in minutes
DbSchema reverse-engineers your database into an interactive diagram and runs SQL against it. Community Edition is free.
