SELECT, WHERE and ORDER BY

Every query in this course is a variation on one statement, so it is worth being precise about the parts before the interesting ones arrive. Most of what surprises people later is not joins or window functions — it is NULL, and it is here.

#The shape, and the order it runs in

select   name, salary          -- 5. which columns come back
from     employee              -- 1. where the rows come from
where    salary > 5000         -- 2. which rows survive
group by department            -- 3. how they are collapsed
having   count(*) > 3          -- 4. which groups survive
order by salary desc           -- 6. how the result is sorted
limit    10;                   -- 7. how many you keep

You write it in that order and the database does not run it in that order — the numbers are the logical sequence. Two consequences follow immediately, and both look arbitrary until you know why:

• where cannot use a column alias. select salary * 12 as annual ... where annual > 100000 fails, because where runs before the select list exists.
• order by can. It runs after, so the alias is there.

That is also why where filters rows and having filters groups: one runs before the grouping, the other after.

#NULL is not a value, and this is where the day goes

NULL means unknown. Not zero, not empty string — unknown. Every comparison with an unknown is itself unknown, and where keeps only rows that are true.

Four employees. Two of them have no manager recorded and one has no salary:

### rows in the table        -> 4
### count(manager)           -> 2
### where manager = null     -> 0
### where manager is null    -> 2
### where manager <> 'bo'    -> 0

Line by line, because each is a bug somebody has shipped:

• count(*) is 4, count(manager) is 2. count(column) counts non-null values. count(*) counts rows. They answer different questions and look like the same one.
• = null returns nothing, ever. Not an error — an empty result. is null is the only test.
• <> 'bo' returns nothing either, and this is the one that catches experienced people. Two employees do not have 'bo' as their manager — but their manager is unknown, and unknown is not provably different from 'bo', so they are excluded. "Not X" silently drops every row where the value is missing. If you want them, ask: where manager <> 'bo' or manager is null.

And then the same question, answered two ways:

### avg(salary)              -> 7000.0
### sum(salary)/count(*)     -> 5250

Both are "the average salary". avg ignores the null row and divides by 3; sum/count(*) divides by 4. Neither is wrong — they answer average of the salaries we know and average across all employees, treating unknown as zero. The bug is not picking one; the bug is not noticing there was a choice.

#Sorting, and where NULL goes

### order by salary, nulls where?
###   cy null
###   ana 5000
###   di 7000
###   bo 9000

The null sorted first here. That is not a rule you can rely on: the SQL standard leaves it to the implementation, PostgreSQL puts nulls last ascending, and others differ. If it matters, say so — order by salary nulls last — rather than discovering the difference when you change database.

#order by and limit belong together

limit 10 without order by means any ten rows. Not the first ten, not the newest ten — whichever ten the plan produced, which can change when the data grows, an index is added, or the plan flips to a parallel scan.

And ordering by a column with ties has the same problem one level down: tied rows may come back in any order, and in a different order on the next query. A paginated query must sort by something unique — usually the id as a final tiebreaker — or rows move between pages. The pagination lesson in the API course is this same fact arriving as a customer complaint.

#distinct is usually a symptom

select distinct is a sorting or hashing step over the whole result, and it is nearly always added to hide duplicates that a join produced:

-- duplicates because each customer has many orders
select distinct c.name from customer c join orders o on o.customer_id = c.id;
 
-- says what you meant, and can stop at the first match
select c.name from customer c where exists (select 1 from orders o where o.customer_id = c.id);

When distinct appears, the useful question is where did the duplicates come from — the answer is usually a join whose cardinality is not what the author thought, and the joins lesson is where that is taken apart.

#Reading before writing

Three habits that make the rest of this course easier:

• select * is fine at a prompt and wrong in code. It fetches columns you do not use, breaks when somebody adds one, and hides the dependency from anyone reading the query.
• Qualify column names once there is more than one table. o.id and c.id are different columns, and an unqualified id is a question the reader has to answer.
• Filter as early as you can express it. The planner will often move it for you, but a query that says what it means is a query you can read a plan for.