$ psql -c "SELECT ..."

Basic SQL

SQL lets you describe the rows you want from one or more tables, and the database works out how to fetch them. A query is built from a few clauses: FROM says which tables, WHERE filters rows, GROUP BY collapses rows into groups, SELECT picks the output columns and ORDER BY sorts. Learn these clauses, joins, and how NULL behaves, and you can answer most everyday questions about data.

The examples use one small table:

CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  city        TEXT,
  status      TEXT,          -- 'paid', 'cancelled', ...
  amount      NUMERIC(10,2),
  ordered_at  TIMESTAMP
);

Reading data

SELECT order_id, city, amount          -- which columns
FROM orders                            -- which table
WHERE status = 'paid' AND amount > 100 -- which rows
ORDER BY amount DESC                   -- sort
LIMIT 10;                              -- how many

Useful filters: IN ('Tehran', 'Shiraz'), BETWEEN 100 AND 500, LIKE 'Teh%', IS NULL. Use SELECT DISTINCT city to remove duplicate rows, and CASE WHEN amount > 500 THEN 'large' ELSE 'small' END for conditional values.

The order SQL actually runs in

You write SELECT first, but the database evaluates it almost last. Knowing the logical processing order explains most beginner errors.

$ EXPLAIN (logically)
Written order vs evaluation order
SELECT city, COUNT(*) AS orders FROM orders WHERE status = 'paid' GROUP BY city HAVING COUNT(*) > 1 ORDER BY orders DESC LIMIT 1; 1 1 · FROM orders8 rows 2 2 · WHERE status = 'paid'6 rows 3 3 · GROUP BY city3 groups 4 4 · HAVING COUNT(*) > 12 groups 5 5 · SELECT (alias "orders")2 rows 6 7 6–7 · ORDER BY, LIMIT 1Tehran · 3
  1. FROM (and any joins) runs first: it builds the full set of input rows. Here, 8 orders.
  2. WHERE filters individual rows. 2 cancelled orders drop out. It cannot use aggregates or SELECT aliases: they don't exist yet.
  3. GROUP BY collapses rows into one row per city: Tehran 3, Shiraz 2, Isfahan 1.
  4. HAVING filters groups, so it can use aggregates. Isfahan, with one paid order, is removed.
  5. SELECT computes the output columns. Only now does the alias orders exist.
  6. ORDER BY runs after SELECT, so it can use the alias. LIMIT keeps the first row: Tehran with 3 paid orders.

This is the logical order. The optimizer may physically execute things differently (push filters into scans, reorder joins), but the result must be as if it followed these steps.

Joins

A join combines rows from two tables where a condition matches, usually a foreign key equal to a primary key.

$ SELECT c.name, o.order_id FROM customers c ? JOIN orders o ON o.customer_id = c.id
INNER, LEFT and FULL OUTER JOIN on the same two tables
customers.idname
1Ali
2Sara
3Reza
order_idcustomer_id
o11
o21
o32
o49
nameorder_idreturned by
Alio1INNER · LEFT · FULL
Alio2INNER · LEFT · FULL
Sarao3INNER · LEFT · FULL
RezaNULLLEFT · FULL: customer with no orders
NULLo4FULL (and RIGHT): order whose customer 9 does not exist
  1. Three customers, four orders. Ali has two orders, Reza has none, and order o4 points to a customer id that doesn't exist.
  2. INNER JOIN returns only matching pairs. Ali appears twice because he has two orders: joins can multiply rows.
  3. LEFT JOIN keeps every customer. Reza has no match, so the order columns are NULL.
  4. FULL OUTER JOIN also keeps unmatched rows from the right side: o4 appears with a NULL customer. RIGHT JOIN is a LEFT JOIN with the tables swapped.

Two more: CROSS JOIN pairs every row with every row (use deliberately, for example with a calendar table), and a self join joins a table to itself (employees and their managers). Anti-joins (“customers with no orders”) are written as LEFT JOIN ... WHERE o.order_id IS NULL or WHERE NOT EXISTS (...).

Aggregation: GROUP BY and HAVING

SELECT city,
       COUNT(*)              AS orders,
       COUNT(DISTINCT customer_id) AS customers,
       SUM(amount)           AS revenue,
       AVG(amount)           AS avg_order
FROM orders
WHERE status = 'paid'
GROUP BY city
HAVING SUM(amount) > 1000
ORDER BY revenue DESC;

Every column in SELECT must either be in GROUP BY or wrapped in an aggregate. WHERE filters rows before grouping, HAVING filters groups after.

NULL: the unknown value

NULL means “unknown”, and SQL uses three-valued logic: comparisons with NULL are neither true nor false but unknown, and WHERE keeps only rows where the condition is true.

  • city = NULL is never true. Use city IS NULL.
  • COUNT(*) counts rows; COUNT(city) counts non-null cities. SUM and AVG ignore NULLs.
  • NOT IN (subquery) returns nothing if the subquery contains a NULL. Prefer NOT EXISTS.
  • COALESCE(city, 'unknown') replaces NULL with a default.

Changing data (CRUD)

INSERT INTO orders (order_id, customer_id, city, status, amount, ordered_at)
VALUES (9001, 42, 'Tehran', 'paid', 80.00, '2026-01-15 10:30');

UPDATE orders SET status = 'cancelled' WHERE order_id = 9001;

DELETE FROM orders WHERE status = 'cancelled' AND ordered_at < '2025-01-01';

Always write the WHERE of an UPDATE or DELETE first, and run it as a SELECT to see which rows it touches. Wrap risky changes in a transaction (BEGIN; ... COMMIT; or ROLLBACK;). Table structure is defined with DDL: CREATE TABLE, ALTER TABLE, DROP TABLE, plus constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK).

Common mistakes

  • Using a SELECT alias in WHERE (it doesn’t exist yet; see the figure).
  • Joining on a non-unique key and silently multiplying rows, then summing them. Check row counts before and after a join.
  • Filtering the right table of a LEFT JOIN in WHERE, which turns it into an inner join. Put the condition in ON instead.
  • = NULL instead of IS NULL, and NOT IN with NULLs.
  • SELECT * in production queries: wasteful on columnar engines and breaks when columns change.

Learning Resources

Books

Interactive

Docs

SQL Fundamentals Advanced SQL