Select and Filter — Three-Valued Logic and the Order of Conditions

In a nutshell

SQL's WHERE lets through only rows that are true, and when NULL is mixed in the result is neither true nor false but unknown, so when you write conditions you must always keep the third value in mind.

Why this was needed

Most programming languages deal with only two values, true and false. SQL deals with three: true, false, and unknown. Every comparison that involves NULL becomes unknown, and since WHERE lets through only rows that are true, unknown is treated like false.

This single difference creates silent bugs in practice. The typical one is a phenomenon in which you flip a condition and the sum of the row counts does not match the total.

SELECT count(*) FROM customers WHERE city = '서울';       -- 48
SELECT count(*) FROM customers WHERE city <> '서울';      -- 335
-- 합이 383 인데 전체는 400 이다. 나머지 17 은 city 가 NULL 인 행이다.

Since NULL rows are not filtered out by <> either, they are on neither side. What you need then is IS DISTINCT FROM. This operator compares NULL like an ordinary value, so in the example above it returns 352.

How it works

The characteristics of frequently used conditions are summarized as follows.

Condition	Meaning	Caution
`BETWEEN a AND b`	At least a and at most b	Both end values are included. Used on a date range, it can drop everything after midnight of the last day
`IN (...)`	One of the list	If the list contains NULL and you use `NOT IN`, the result disappears entirely
`LIKE 'kim%'`	Prefix match	Index search is possible
`LIKE '%kim%'`	Partial match	The starting point is unknown, so a regular index cannot search it
`IS NULL`	No value	`= NULL` is always 0 rows
`COALESCE(a, b)`	b if a is NULL	Use it for display substitution; if used in a condition, it cannot use an index

CASE picks the first branch from the top that is true. So when you list conditions with overlapping boundaries, the order is the priority. This is why the convention when dividing amount ranges is to write the larger values first.

Pagination also needs a mention. Without ORDER BY, using only LIMIT does not guarantee which rows come out. And if there are ties in the sort key, rows are duplicated or missing between pages. A sort must always include a unique value to create a tie-break. In practice you attach the primary key at the end, as in ORDER BY created_at DESC, id DESC.

What it looks like in the field

OFFSET-style pagination gets slower toward the later pages. This is because OFFSET 10000 reads ten thousand rows and throws them away. The alternative is the cursor approach, which continues reading from the last value seen. The condition takes the form WHERE (created_at, id) < (마지막값, 마지막id) (last value, last id), and an index search finds the starting point immediately.

The three-valued logic that NULL creates

SQL comparisons are not true or false but true, false, or unknown. This changes how conditions behave.

NULL = NULL      → UNKNOWN (참이 아니다)
NULL <> 1        → UNKNOWN
NULL IS NULL     → TRUE      ← 이것만 참이 된다

WHERE keeps only rows that are true. UNKNOWN is thrown away like false. So this happens.

-- status 가 NULL 인 행은 두 질의 어디에도 안 나온다
select * from orders where status = 'done';
select * from orders where status <> 'done';

To cover everything, you must spell out NULL.

where status is distinct from 'done'    -- NULL 도 "다르다" 로 본다
where status <> 'done' or status is null
where coalesce(status, '') <> 'done'

IS DISTINCT FROM is the cleanest. It compares NULL like an ordinary value.

Aggregation is different too. count(*) counts rows, but count(col) counts only non-NULL values. avg and sum also ignore NULL, so if you need to treat missing values as 0, you must fill them in with coalesce.

The order of conditions does not change performance (usually)

In WHERE a = 1 AND b = 2, the optimizer decides by itself even if you swap the order. What a person should care about is not the order but whether the form can use an index.

-- ❌ 인덱스를 못 쓴다 — 컬럼에 함수를 씌웠다
where date(created_at) = '2026-09-06'
where upper(email) = 'A@B.COM'

-- ✅ 범위로 바꾸거나 표현식 인덱스를 만든다
where created_at >= '2026-09-06' and created_at < '2026-09-07'
create index on users ((upper(email)));

LIKE is the same. 'abc%' uses an index but '%abc' cannot. If you need to search from the end, use a trigram index or full-text search.

The minimum for reading an execution plan

explain (analyze, buffers) select …;

analyze actually runs it, and buffers shows how much was read. There are three things to look at.

Seq Scan vs Index Scan — if a large table gets a Seq Scan, suspect the index. However, if the table is small, a Seq Scan is faster.
The rows= estimate and actual rows — if they differ greatly, the statistics are stale (ANALYZE).
Buffers: shared read — the amount read from disk. If there are many hits, the cache is working well.

What you will do in the next lab

On a real e-commerce schema, you will use BETWEEN, IN, LIKE, COALESCE, CASE, and IS DISTINCT FROM in turn. In particular, you will experience for yourself the row counts not adding up when you flip a condition on a column that contains NULL.