Skip to content
pitonmert

Everyday SQL: Quick Reference

This page is for developers who want to look up everyday SQL syntax. The examples use PostgreSQL syntax and are independent of one another. Each small data description shows the state before its query; the result shows either returned rows or the state afterward. Adapt the table and column names to your own database when running a query.

How to use this page

Jump to selecting rows, filtering, sorting, joining tables, grouping, changing data, or managing transactions. For the guarantees behind transactions, see the ACID article.

SELECT and DISTINCT

SELECT chooses the columns to return. DISTINCT removes duplicate result rows formed by the selected columns.

Data: customers(id, city) → (1, Ankara), (2, Bursa), (3, Ankara)

SELECT DISTINCT city
FROM customers
ORDER BY city;

Result: Ankara, Bursa

With several selected columns, DISTINCT compares the whole row, not each column independently.

WHERE and NULL

WHERE limits which rows a query processes. NULL represents an unknown or missing value; use IS NULL instead of = NULL.

Data: tasks(id, title, completed_at) → (1, Write, NULL), (2, Review, 2026-09-20)

SELECT id, title
FROM tasks
WHERE completed_at IS NULL
ORDER BY id;

Result: 1 | Write

NULL is distinct from an empty string, zero, or false.

ORDER BY and LIMIT

ORDER BY sorts the result; ASC is ascending and DESC is descending. LIMIT caps the number of returned rows.

Data: products(id, price) → (1, 40), (2, 20), (3, 40)

SELECT id, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 2;

Result: 1 | 40, 3 | 40

id breaks the price tie. Row order is not guaranteed without ORDER BY; do not rely on an implicit order when paginating.

JOIN

INNER JOIN returns only matching rows. LEFT JOIN also keeps unmatched rows from the left table and fills the right-hand columns with NULL.

Data: customers(id, name) → (1, Ada), (2, Ece); orders(id, customer_id) → (10, 1)

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Result: Ada | 10, Ece | NULL

Adding WHERE o.id IS NOT NULL removes the unmatched customer and may defeat the reason for choosing a LEFT JOIN.

GROUP BY and HAVING

GROUP BY collects rows with the same key. COUNT(*) counts rows, and SUM totals numeric values. WHERE filters rows before grouping; HAVING filters groups afterward.

Data: orders(customer_id, amount) → (1, 20), (1, 30), (2, 10)

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Result: 1 | 2 | 50

Unlike COUNT(*), COUNT(column) does not count NULL values in that column.

INSERT, UPDATE, and DELETE

These commands change data. Each miniature example below has its own starting state.

INSERT: products(id, name) starts empty.

INSERT INTO products (id, name) VALUES (1, 'Pen');

After: 1 | Pen

UPDATE: products(id, price) → (1, 20), (2, 30)

UPDATE products SET price = 25 WHERE id = 1;

After: (1, 25), (2, 30)

DELETE: products(id, name) → (1, Pen), (2, Notebook)

DELETE FROM products WHERE id = 1;

After: 2 | Notebook

Check the WHERE clause before UPDATE or DELETE; without it, the command may affect every row. Bind application values as parameters instead of concatenating them into SQL text.

BEGIN, COMMIT, and ROLLBACK

BEGIN groups commands into a transaction. COMMIT completes it; ROLLBACK cancels changes in an unfinished transaction. The total balance in this example stays the same.

Data: accounts(owner, balance) → (Ada, 70), (Ece, 30)

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE owner = 'Ada';
UPDATE accounts SET balance = balance + 10 WHERE owner = 'Ece';
COMMIT;

After: (Ada, 60), (Ece, 40)

Running ROLLBACK in place of COMMIT leaves neither change in place. ROLLBACK cannot undo an earlier committed transaction. The ACID article covers transaction guarantees and their limits.

Sources