WHERE Deep Dive ① — AND, OR, NOT for Building Compound Conditions

Data we'll use — the staff table

Starting with this article, we'll spend three articles taking a deeper look at WHERE filtering patterns. The first is the logical operators for building compound conditions — AND (and) / OR (or) / NOT (not) — plus the operator precedence and parentheses that come into play when you mix them.

The topic dataset is the staff table (10 rows) loaded from CSV, with name / city / salary / birthday columns. We'll build common real-world conditions like "Tokyo and high-paid," "Tokyo or Osaka," and "not in Kyoto" using each operator.

Before the exercises, confirm the staff table's column definitions and a sample of the data.

① Run PRAGMA table_info(staff); to check column names, types, and primary keys.

② Run SELECT * FROM staff LIMIT 5; to preview the first 5 rows.

AND, OR, NOT — building conditions with logical operators

Logical operators combine multiple conditions in a single WHERE. There are three of them.

- `AND`: TRUE only when both conditions are TRUE

- `OR`: TRUE when at least one of the conditions is TRUE

- `NOT`: negates the condition that follows it (TRUE → FALSE / FALSE → TRUE)

The syntax is just inserting the keyword: WHERE cond1 AND cond2, WHERE cond1 OR cond2, WHERE NOT (cond). You can chain AND / OR as many times as you need, and NOT goes in front of a single condition.

The basic rule is: AND is TRUE only when both are TRUE; OR is TRUE when either one is TRUE. The behavior when NULL gets mixed into a comparison is covered in two articles from now under three-valued logic, but for this article we stay in the 2-value world (TRUE / FALSE) and just learn how to compose conditions.

-- 1) AND: meets both (Tokyo and high-paid)
SELECT name, city, salary FROM staff
WHERE city = 'Tokyo' AND salary >= 5000000;

-- 2) OR: meets either (Tokyo or Osaka)
SELECT name, city FROM staff
WHERE city = 'Tokyo' OR city = 'Osaka';

-- 3) NOT: negates the condition (anywhere but Kyoto)
SELECT name, city FROM staff
WHERE NOT (city = 'Kyoto');

-- NOT can also be written with != or <>
SELECT name, city FROM staff
WHERE city != 'Kyoto';

Imagine a bonus review needs "Tokyo-based staff with salary at least 5,000,000." (Once you run it correctly, the explanation will appear.)

① From staff, pull the name, city, and salary columns.

② Filter to rows where `city` is `'Tokyo'` AND `salary` is at least 5,000,000.

③ Confirm the result is 3 rows (David Sato 6,800,000 / Grace Suzuki 5,500,000 / Jack Tanaka 5,900,000).

Imagine a notice needs to go to "staff based in either the Kanto (Tokyo) or Kansai (Osaka) region."

① From staff, pull the name and city columns.

② Filter to rows where `city` is `'Tokyo'` OR `'Osaka'` (use OR).

③ Confirm the result is 6 rows (Alice Tokyo / Bob Osaka / David Tokyo / Frank Osaka / Grace Tokyo / Jack Tokyo).

Operator precedence — AND binds tighter than OR

The one rule you absolutely need to know when you mix AND and OR is that `AND` has higher precedence than `OR`. Writing A OR B AND C is interpreted as `A OR (B AND C)` — not left-to-right in writing order.

This often produces results that don't match your intent, so when you mix AND and OR, always make the evaluation order explicit with parentheses. Parentheses don't just change SQL semantics — they also signal your intent to the reader — so even when the meaning is the same with or without them, it's a good habit to put them in.

`AND` is evaluated before `OR`

A OR B AND C is interpreted as `A OR (B AND C)`. AND binds tighter than OR, so it doesn't read left-to-right in writing order.

Example: WHERE city = 'Tokyo' OR city = 'Osaka' AND salary >= 5000000 means `city = 'Tokyo' OR (city = 'Osaka' AND salary >= 5000000)`, so all Tokyo staff stay regardless of salary (Alice Tokyo 4,500,000 is included). If you mean "high-paid among Tokyo or Osaka," always write (city = 'Tokyo' OR city = 'Osaka') AND salary >= 5000000 and make the evaluation order explicit with parentheses.

Imagine a need for "staff in Tokyo or Osaka with salary at least 5,000,000." In a single console, compare the result with parentheses versus without to see how it changes.

① From staff, pull the name, city, and salary columns.

② First, write the with-parentheses, intended form ((city = 'Tokyo' OR city = 'Osaka') AND salary >= 5000000).

③ Then deliberately drop the parentheses (city = 'Tokyo' OR city = 'Osaka' AND salary >= 5000000).

④ Confirm the first returns 5 rows and the second returns 6 rows (Alice Tokyo 4,500,000 is added) — the result changes.

Imagine a need to "build a list of all staff except those based in Kyoto."

① From staff, pull the name and city columns.

② Filter to rows where `city` is not `'Kyoto'` (use NOT (condition)).

③ Confirm the result is 8 rows (Carol and Iris are excluded).