Combining conditions

What do AND and OR mean in a WHERE clause?

AND joins two conditions and keeps a row only when both are true. OR keeps a row when at least one is true. A row at Mill Pond with 14 Mallards passes place = 'Mill Pond' AND bird_count >= 3. A row at Mill Pond with one Grey heron fails it, because the second half is false.

This lesson uses a bird club's April sightings. Each row records a species, the place, how many birds, the date, whether the club counts it as rare (1 for yes, 0 for no) and who saw it.

As a rule of thumb, AND narrows the result and OR widens it. Each extra AND can only remove rows; each extra OR can only add them.

SELECT species, place, bird_count
FROM sightings
WHERE place = 'Mill Pond'
  AND bird_count >= 3;

When do you use OR?

Use OR for alternatives: sightings at Mill Pond or Heron Marsh, birds that are rare or seen in big numbers. Each side of OR must be a complete condition. place = 'Mill Pond' OR 'Heron Marsh' runs without an error, but the second half is just a piece of text, which SQLite treats as false, so it matches Mill Pond only.

A common slip is reaching for AND because the English sentence says "and": "show me sightings at Mill Pond and Heron Marsh". A single row has one place, so place = 'Mill Pond' AND place = 'Heron Marsh' can never be true and returns nothing. When two tests are on the same column, you almost always want OR, or the IN list from the next lesson.

SELECT species, place
FROM sightings
WHERE place = 'Mill Pond'
  OR place = 'Heron Marsh';

How does NOT work?

NOT flips the condition that follows it. NOT rare = 1 keeps the rows where rare = 1 is false. For a single comparison, the opposite operator usually reads better: rare <> 1 or rare = 0.

NOT earns its place in front of a bracketed group. NOT (place = 'Mill Pond' OR place = 'Heron Marsh') means "anywhere except those two places". You could write it as place <> 'Mill Pond' AND place <> 'Heron Marsh'. Notice the OR became an AND once each test was flipped. People often get that swap wrong, and place <> 'Mill Pond' OR place <> 'Heron Marsh' is true for every row, because no row is in both places.

SELECT species, place
FROM sightings
WHERE NOT (place = 'Mill Pond' OR place = 'Heron Marsh');

Why does AND happen before OR?

SQL has an order of operations for conditions, like multiplication before addition in arithmetic. NOT is applied first, then AND, then OR. That means a OR b AND c is read as a OR (b AND c).

Try the task without brackets: WHERE place = 'Mill Pond' OR place = 'Heron Marsh' AND bird_count >= 3. You get eight rows instead of five. The database read it as "anything at Mill Pond, or busy rows at Heron Marsh", so the single Grey heron and the single Great white egret at Mill Pond both slip in. No error appears; the result is wrong and looks reasonable. This is the classic AND/OR bug, and it turns up in queries written by people and by AI tools all the time.

-- Without brackets: AND grabs only the second place test
SELECT id, species, place, bird_count
FROM sightings
WHERE place = 'Mill Pond' OR place = 'Heron Marsh'
  AND bird_count >= 3;

How do brackets fix it?

Brackets group conditions so they are decided first, just as in arithmetic. (place = 'Mill Pond' OR place = 'Heron Marsh') AND bird_count >= 3 first works out whether the place is one of the two, then requires the count as well.

The safe habit is to put brackets around every OR group whenever AND is also in the condition. Even when the brackets change nothing, they tell the reader what you meant. A query that mixes AND and OR without brackets is one that needs a second look, whoever wrote it.

Open the Rows view on the bracketed version. The WHERE step shows each sighting that was dropped and the reason, so you can check that every Mill Pond row with only one or two birds is crossed out.

SELECT id, species, place, bird_count
FROM sightings
WHERE (place = 'Mill Pond' OR place = 'Heron Marsh')
  AND bird_count >= 3;

How do you read a long condition?

Break it at the top-level ANDs. Each line of a well laid out WHERE clause is one test that every row must pass. Inside a bracketed group, the ORs are alternatives. Then read it back as a sentence: "rare, and at Mill Pond or Clee Hill, and not seen by Asha".

Lay conditions out one per line, starting each line with AND or OR, and indent the continuation lines. That makes the structure visible before you read a single value. When a condition gives the wrong rows, comment out one line at a time with -- and watch how the result changes. The line that changes it the wrong way is your bug.

SELECT species, place, observer
FROM sightings
WHERE rare = 1
  AND (place = 'Mill Pond' OR place = 'Clee Hill')
  AND observer <> 'Asha';

How do you test a condition you are unsure about?

Pick two or three rows from the Tables view and work them through the condition by hand. Take row 1: Grey heron, Mill Pond, 1 bird. Is the place one of the two? Yes. Is the count 3 or more? No. True AND false is false, so the row should be dropped. If the result disagrees with your hand check, the brackets are in the wrong place.

Another trick is to put the condition in the SELECT list. SQLite shows a true condition as 1 and a false one as 0, so you can see each part's answer for every row side by side.

SELECT
  id,
  place,
  bird_count,
  place = 'Mill Pond' OR place = 'Heron Marsh' AS wetland,
  bird_count >= 3 AS busy
FROM sightings;

What should you check in conditions an AI wrote?

Look for OR next to AND without brackets. Look for AND between two values of the same column, which can never both be true. Look for <> combined with OR, which is almost always true for every row. And check that text matches the stored spelling and capitals. These four checks catch most combined-condition bugs in under a minute. Then run the query and compare the row count with the number you expected from a glance at the Tables view.

Field notes

• AND keeps a row only when both sides are true; OR needs just one side.
• Each side of OR must be a full condition, such as place = 'Heron Marsh'.
• AND is applied before OR, which can change what a query keeps without any error.
• Put brackets around every OR group when AND is also in the condition.

Easy to get wrong

AND is applied before OR, so the count test only applies to Heron Marsh. Brackets make it apply to both places.

One row has one place, so both tests can never be true together. Alternatives for the same column need OR.

Every row differs from at least one of the two places, so the OR version keeps everything. To exclude both, both tests must hold.

Each side of OR needs its own comparison. The bare text counts as false in SQLite, so Heron Marsh rows are silently missed.

Questions people ask

Can I mix AND and OR without brackets if I know the rules?

It works, but the next reader has to know the rules too. Brackets cost nothing and remove the doubt.

Is there a limit to how many conditions I can combine?

Not one you will meet in practice. Long conditions are a readability problem long before they are a database problem.

Why does SQLite show 1 and 0 for conditions?

SQLite has no separate true/false type. True is stored and shown as 1, false as 0. PostgreSQL has a real boolean type and shows t and f or true and false.

Does the order of conditions joined by AND matter?

Not for the result. a AND b keeps the same rows as b AND a. The database may also check them in whichever order is cheaper.

What does NOT do with a missing value?

A comparison with NULL is unknown, and NOT unknown is still unknown, so the row is dropped either way. The lesson on missing values explains this three-valued logic.