# 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:
```sql
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
```sql
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.
{{< fig kicker="$ EXPLAIN (logically)" title="Written order vs evaluation order" >}}
FROM (and any joins) runs first: it builds the full set of input rows. Here, 8 orders.
WHERE filters individual rows. 2 cancelled orders drop out. It cannot use aggregates or SELECT aliases: they don't exist yet.
GROUP BY collapses rows into one row per city: Tehran 3, Shiraz 2, Isfahan 1.
HAVING filters groups, so it can use aggregates. Isfahan, with one paid order, is removed.
SELECT computes the output columns. Only now does the alias orders exist.
ORDER BY runs after SELECT, so it can use the alias. LIMIT keeps the first row: Tehran with 3 paid orders.
{{< /fig >}}
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.
{{< fig kicker="$ SELECT c.name, o.order_id FROM customers c ? JOIN orders o ON o.customer_id = c.id" title="INNER, LEFT and FULL OUTER JOIN on the same two tables" >}}
customers.id
name
1
Ali
2
Sara
3
Reza
order_id
customer_id
o1
1
o2
1
o3
2
o4
9
name
order_id
returned by
Ali
o1
INNER · LEFT · FULL
Ali
o2
INNER · LEFT · FULL
Sara
o3
INNER · LEFT · FULL
Reza
NULL
LEFT · FULL: customer with no orders
NULL
o4
FULL (and RIGHT): order whose customer 9 does not exist
Three customers, four orders. Ali has two orders, Reza has none, and order o4 points to a customer id that doesn't exist.
INNER JOIN returns only matching pairs. Ali appears twice because he has two orders: joins can multiply rows.
LEFT JOIN keeps every customer. Reza has no match, so the order columns are NULL.
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.
{{< /fig >}}
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
```sql
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)
```sql
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
- [Learning SQL, 3rd Edition](https://www.oreilly.com/library/view/learning-sql-3rd/9781492057604/): Alan Beaulieu
- [Sams Teach Yourself SQL in 10 Minutes](https://www.amazon.com/SQL-Minutes-Sams-Teach-Yourself/dp/0672336073): Ben Forta, short and hands-on
### Interactive
- [SQLBolt](https://sqlbolt.com/)
- [Select Star SQL](https://selectstarsql.com/)
- [SQLZoo](https://www.sqlzoo.net/wiki/SQL_Tutorial)
- [Mode SQL Tutorial](https://mode.com/sql-tutorial)
### Docs
- [PostgreSQL Tutorial: The SQL Language](https://www.postgresql.org/docs/current/tutorial-sql.html)
- [PostgreSQL: Joins Between Tables](https://www.postgresql.org/docs/current/tutorial-join.html)