SELECT — Reading & Filtering¶

SELECT is the R in CRUD — and the operation you'll run most.

The Shape of a Query¶

sql

5 lines

1SELECT   title, status        -- which columns
2FROM     tasks                -- which table
3WHERE    status = 'TODO'      -- which rows
4ORDER BY due_date ASC         -- in what order
5LIMIT    20;                  -- how many

SQL executes roughly in this order: FROM → WHERE → SELECT → ORDER BY → LIMIT.

Select Only What You Need¶

sql

2 lines

1SELECT id, title FROM tasks;   -- ✅ explicit
2SELECT * FROM tasks;           -- ⚠️ avoid in production code

SELECT * ships every column over the wire and breaks when the schema changes. Name your columns.

Filtering with WHERE¶

sql

6 lines

1WHERE status = 'DONE'
2WHERE due_date < now()
3WHERE status IN ('TODO', 'IN_PROGRESS')
4WHERE title ILIKE '%report%'        -- case-insensitive contains
5WHERE due_date IS NULL              -- never use = NULL
6WHERE status = 'TODO' AND due_date < now()

Sorting & Limiting¶

sql

2 lines

1ORDER BY created_at DESC      -- newest first
2LIMIT 10 OFFSET 20;          -- page 3 of 10-per-page

Aggregates¶

sql

3 lines

1SELECT status, COUNT(*) AS n
2FROM tasks
3GROUP BY status;

GROUP BY collapses rows into buckets; aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarize each bucket.

sql

4 lines

1SELECT t.title, u.name AS owner
2FROM tasks t
3JOIN users u ON u.id = t.owner_id
4WHERE t.status = 'TODO';

A JOIN stitches rows from two tables together on a matching condition — here, the task's owner_id to the user's id.