SQL Commands

SQL statements fall into four groups. DML (data manipulation) reads and changes rows: SELECT, INSERT, UPDATE, DELETE, the four CRUD operations (SELECT is sometimes singled out as DQL). DDL (data definition) changes the structure: CREATE, ALTER, DROP, TRUNCATE. DCL (data control) manages access: GRANT, REVOKE. TCL (transaction control) groups statements into transactions: BEGIN, COMMIT, ROLLBACK, SAVEPOINT. In interviews, most questions are about SELECT: JOIN, GROUP BY / HAVING and the order in which clauses are evaluated.

Applies to: PostgreSQL 18.

Details

The examples on this page use two tables:

CREATE TABLE users (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  email text NOT NULL UNIQUE
);

CREATE TABLE orders (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id integer REFERENCES users (id), -- NULL: a guest order
  total numeric(10, 2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (name, email) VALUES
  ('Ada', 'ada@example.com'),
  ('Linus', 'linus@example.com'),
  ('Grace', 'grace@example.com');   -- Grace has no orders

INSERT INTO orders (user_id, total) VALUES (1, 30), (1, 70), (2, 15), (NULL, 5);

SELECT

Syntax

SELECT [DISTINCT] column, expression AS alias, aggregate(column)
FROM table_a
[INNER | LEFT | RIGHT | FULL] JOIN table_b ON condition
WHERE row_condition
GROUP BY column
HAVING group_condition
ORDER BY column [ASC | DESC]
LIMIT n OFFSET m;

Example

SELECT name, email
FROM users
WHERE name LIKE 'A%'
ORDER BY name
LIMIT 10;

The clauses are written in one order and evaluated in another:

Step	Clause	What it does
1	FROM, JOIN	Builds the source rows
2	WHERE	Filters rows (no aggregates here)
3	GROUP BY	Groups rows
4	HAVING	Filters groups (aggregates allowed)
5	SELECT	Computes the output columns and their aliases, then window functions
6	DISTINCT	Removes duplicate output rows
7	ORDER BY	Sorts (can use aliases from SELECT)
8	LIMIT / OFFSET	Cuts the result

That is why an alias from SELECT cannot be used in WHERE, and why a window function such as row_number() cannot be filtered in WHERE (wrap the query in a subquery or CTE):

SELECT name AS n FROM users WHERE n = 'Ada'; -- ERROR: column "n" does not exist
SELECT name AS n FROM users WHERE name = 'Ada';

JOIN

Join	Returns
INNER JOIN (JOIN)	Only pairs of rows that match the condition
LEFT JOIN	All rows of the left table; NULLs where the right one has no match
RIGHT JOIN	All rows of the right table; usually rewritten as a LEFT JOIN
FULL JOIN	All rows of both tables, matched where possible
CROSS JOIN	Every row with every row (Cartesian product), no condition
Self join	A table joined with itself under two aliases, e.g. employee and manager

Examples

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY u.name, o.total;
-- Ada   | 30.00
-- Ada   | 70.00
-- Linus | 15.00

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.name, o.total;
-- Ada   | 30.00
-- Ada   | 70.00
-- Grace | NULL      <- no orders
-- Linus | 15.00

SELECT u.name, o.total
FROM users u
FULL JOIN orders o ON o.user_id = u.id
ORDER BY u.name, o.total;
-- the same plus: NULL | 5.00  <- the guest order

• A condition on the right table in WHERE turns a LEFT JOIN into an inner one: WHERE o.total > 10 drops the rows where o.total is NULL. Put it in ON to keep all left rows.
• Rows "without a match": LEFT JOIN … WHERE o.id IS NULL, or NOT EXISTS.

GROUP BY and Aggregates

• Aggregates: count, sum, avg, min, max, string_agg, array_agg.
• count(*) counts rows; count(column) counts non-NULL values.
• Every column in SELECT must be in GROUP BY or inside an aggregate (PostgreSQL also allows other columns of a table grouped by its primary key).
• WHERE filters rows before grouping, HAVING filters groups after.

Examples

SELECT u.name, count(o.id) AS orders, coalesce(sum(o.total), 0) AS spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
HAVING count(o.id) < 2
ORDER BY spent DESC;
-- Linus | 1 | 15.00
-- Grace | 0 | 0

count(*) would give Grace 1: the LEFT JOIN produced one row for her.

Subqueries and CTEs

• A subquery is a SELECT inside another statement: in WHERE (IN, EXISTS), in FROM or as a single value.
• A CTE (WITH name AS (…)) names a subquery so the main query reads top to bottom; WITH RECURSIVE walks trees (comments, categories).
• Window functions (row_number() OVER (PARTITION BY … ORDER BY …)) compute values across related rows without collapsing them into groups.

Examples

WITH spent AS (
  SELECT user_id, sum(total) AS total
  FROM orders
  GROUP BY user_id
)
SELECT u.name, s.total
FROM users u
JOIN spent s ON s.user_id = u.id
WHERE s.total > 20;
-- Ada | 100.00

SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders);
-- 0 rows: the subquery contains NULL, and x NOT IN (…, NULL) is never true

SELECT name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- Grace

UNION, INTERSECT, EXCEPT

Combine the results of two queries with the same number and types of columns:

Operator	Returns
UNION	Rows from either query, duplicates removed
UNION ALL	Rows from either query, duplicates kept; faster
INTERSECT	Rows present in both
EXCEPT	Rows of the first query that are not in the second

Examples

SELECT user_id FROM orders WHERE total > 20
UNION
SELECT user_id FROM orders WHERE total < 20
ORDER BY user_id;
-- 1, 2, NULL

-- the same with UNION ALL: 1, 1, 2, NULL (Ada has two orders over 20)

INSERT

Syntax

INSERT INTO table_name (column1, column2)
VALUES (value1, value2), (value3, value4)
[ON CONFLICT (column) DO NOTHING | DO UPDATE SET column = EXCLUDED.column]
[RETURNING column];

Example

-- Upsert: insert, or update if the email already exists
INSERT INTO users (name, email)
VALUES ('Ada L.', 'ada@example.com')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING id, name;
-- 1 | Ada L.

• RETURNING gives back the inserted rows, for example the generated id, without a second query. It works with UPDATE and DELETE too; since PostgreSQL 18, RETURNING old.total, new.total returns values before and after the change.
• Always list the columns: INSERT INTO t VALUES (…) breaks when a column is added.

UPDATE

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example

UPDATE orders
SET total = total * 0.9
WHERE user_id = 1
RETURNING id, total;

DELETE

Syntax

DELETE FROM table_name WHERE condition;

Example

DELETE FROM orders WHERE user_id IS NULL;

Without WHERE, UPDATE and DELETE affect every row of the table.

DDL

Command	What it does
CREATE	Creates a table, index, view, schema and other objects
ALTER	Changes an object: ALTER TABLE users ADD COLUMN age integer
DROP	Deletes an object with its data: DROP TABLE users
TRUNCATE	Removes all rows at once, keeping the table

DELETE vs TRUNCATE vs DROP:

	DELETE	TRUNCATE	DROP
Removes	Rows matching WHERE	All rows	The table itself
Speed on big tables	Slow: row by row	Fast: replaces the data files	Fast
Triggers	Row DELETE triggers fire	Only TRUNCATE triggers	—
Identity counter	Kept	Kept, or reset with RESTART IDENTITY	Gone with the table
In a transaction	Can be rolled back	Can be rolled back in PostgreSQL	Can be rolled back in PostgreSQL

DCL and TCL

Command	What it does
GRANT	Gives privileges: GRANT SELECT ON users TO analyst
REVOKE	Takes them back

Transaction commands (BEGIN, COMMIT, ROLLBACK, SAVEPOINT) are in Transactions.

Follow-up Questions

• What is the difference between WHERE and HAVING? WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after.
• INNER JOIN or LEFT JOIN? LEFT JOIN when rows of the left table must be kept even without a match (users without orders).
• How do you find rows that have no related rows? NOT EXISTS, or LEFT JOIN … WHERE right.id IS NULL. Avoid NOT IN with a subquery that can return NULL.
• How do you paginate? LIMIT … OFFSET … is simple but slows down on deep pages; keyset pagination (WHERE id > last_seen_id ORDER BY id LIMIT 20) stays fast.
• How do you avoid SQL injection? Parameterized queries (WHERE id = $1 with the value passed separately), never string concatenation.

Common Mistakes

• Filtering the right table of a LEFT JOIN in WHERE and losing the unmatched rows.
• count(*) after a LEFT JOIN when the rows without a match should count as 0: use count(right.id).
• = NULL instead of IS NULL: any comparison with NULL is NULL, not true.
• UPDATE or DELETE without WHERE.
• Using a SELECT alias in WHERE.

See Also

• Relationships Between Tables
• Constraints
• Transactions