AND keeps a record only if both conditions are true. OR keeps it if at least one is true. Brackets force a part of the condition to be worked out first.
Without brackets, AND is worked out before OR. That is why bracket placement is a favourite exam trap in databases and query reasoning.
How do the operators behave?
| A | B | A AND B | A OR B |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| FALSE | TRUE | FALSE | TRUE |
| FALSE | FALSE | FALSE | FALSE |
Adding an AND makes a query stricter, so it can only remove rows. Adding an OR makes it more generous, so it can only add rows. The Boolean and number-representation lab builds truth tables like this one.
Worked example
Here is the Plants table.
| PlantID | Name | Type | Price | Stock | Indoor |
|---|---|---|---|---|---|
| P01 | Fern | Foliage | 12.50 | 8 | TRUE |
| P02 | Cactus | Succulent | 6.00 | 25 | TRUE |
| P03 | Rose | Flowering | 15.00 | 0 | FALSE |
| P04 | Mint | Herb | 4.50 | 40 | FALSE |
| P05 | Orchid | Flowering | 28.00 | 3 | TRUE |
| P06 | Basil | Herb | 5.00 | 12 | FALSE |
| P07 | Aloe | Succulent | 9.50 | 15 | TRUE |
| P08 | Hibiscus | Flowering | 18.00 | 6 | FALSE |
Compare two queries.
SELECT Name FROM Plants
WHERE Type = "Herb" OR Type = "Succulent" AND Price > 6SELECT Name FROM Plants
WHERE (Type = "Herb" OR Type = "Succulent") AND Price > 6Query 1. AND first, so the condition means: Herb, OR (Succulent AND Price > 6). Mint and Basil are herbs, so both pass. Cactus is a succulent but 6.00 is not above 6, so it fails. Aloe is a succulent at 9.50, so it passes. Result: Mint, Basil, Aloe (3 rows).
Query 2. Brackets first: the plant must be a Herb or a Succulent, and cost more than 6. Mint 4.50 fails, Basil 5.00 fails, Cactus 6.00 fails, Aloe 9.50 passes. Result: Aloe (1 row).
Same words, different brackets, three rows against one.
The mistake to watch for
A common slip is to read a condition left to right and ignore AND’s priority.
Mistaken answer: “Query 1 returns only Aloe, because the price test applies to herbs and succulents.”
That reading adds brackets which the query does not have.
The correction: write the brackets in yourself, around the AND, before testing a single row. When you write a query, always add brackets, even where they are optional.
Check yourself
1. Which plants does WHERE Type = "Flowering" AND Indoor = TRUE return?
Show answer
Flowering: Rose (outdoor), Orchid (indoor), Hibiscus (outdoor). Only Orchid passes both.
2. How many rows does WHERE Stock = 0 OR Price > 20 return?
Show answer
Stock = 0: Rose. Price > 20: Orchid. Together 2 rows: Rose and Orchid.
3. Rewrite Type = "Herb" OR Type = "Foliage" AND Indoor = TRUE so that the Indoor test applies to both types.
Show answer
(Type = "Herb" OR Type = "Foliage") AND Indoor = TRUEWhere this leads next
Next, learn to explain why a query returns no rows. The module practice set includes more grouping questions.
If AND and OR still go wrong under exam pressure, a teacher in online one-to-one Computer Science tuition can give you new queries to bracket and test row by row.