Sql · Filtering Results
AND, OR, NOT
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Combining Conditions with Logical Operators
In SQL, we can filter data using the WHERE clause. Sometimes, we need to apply multiple conditions at once. This is where logical operators come in:
- AND: All conditions must be true
- OR: At least one condition must be true
- NOT: Negates a condition
Exercise 2 — Using AND Operator
The AND operator is used when you want to find records that satisfy multiple conditions. All conditions connected by AND must be true for a row to be included in the results.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
Example: Find Codémons that are both fire type AND have a level higher than 15:
SELECT * FROM codemon
WHERE type = 'fire' AND level > 15;
In this example, only Pyrochu is returned because it's the only Codémon that satisfies both conditions: it's a fire type AND has a level higher than 15.
AND is Restrictive
Remember that AND narrows down your results. Each additional AND condition reduces the number of rows returned. A row must satisfy ALL conditions to be included in the results.
Exercise 3 — Understanding AND Logic
Given our Codémon table, which of the following SQL queries will return Florachu?
Options
• SELECT * FROM codemon
WHERE type = 'plant'
AND level < 20;
• SELECT * FROM codemon
WHERE type = 'plant'
AND power > 80;
• SELECT * FROM codemon
WHERE type = 'plant'
AND level = 18;
• SELECT * FROM codemon
WHERE type = 'fire'
AND power = 70;
Hint : Think about which conditions Florachu satisfies. Check its type and level in the table.
Exercise 4 — Using OR Operator
The OR operator is used when you want to find records that satisfy at least one of multiple conditions. If any condition connected by OR is true, the row will be included in the results.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Example: Find Codémons that are either water type OR have a power greater than 80:
SELECT * FROM codemon
WHERE type = 'water' OR power > 80;
In this example, both Aquachu and Firochu are returned. Aquachu is included because it's a water type, and Firochu is included because its power is greater than 80.
OR is Inclusive
Unlike AND, OR broadens your results. Each additional OR condition increases the potential number of rows returned. A row only needs to satisfy ONE of the conditions to be included in the results.
Exercise 5 — Complete the OR Query
Complete the SQL query to find all Codémons that are either :
- fire type
- level of at least 20.
SELECT *
FROM codemon
WHERE type = ____ ____ level ____ 20;
Options: 'fire' OR AND >= <= NOT
Hint : For the OR operator, either condition can be true for a row to be included. Make sure to use the correct comparison operator for 'at least 20'.
Exercise 6 — Using NOT Operator
The NOT operator is used to negate a condition. It returns rows where the specified condition is NOT true.
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Example: Find all Codémons that are NOT water type:
SELECT * FROM codemon
WHERE NOT type = 'water';
This returns all Codémons except Aquachu, because it's the only water type Codémon in our table.
Alternatives to NOT
Sometimes, you can rewrite NOT conditions using different comparison operators. For example, NOT level < 18 is the same as level >= 18, and NOT type = 'water' is the same as type != 'water'.
Exercise 7 — Understanding NOT Logic
Which of the following queries is equivalent to:
SELECT * FROM codemon WHERE NOT power <= 75;
Options
• SELECT * FROM codemon
WHERE power > 75;
• SELECT * FROM codemon
WHERE power = 75;
• SELECT * FROM codemon
WHERE power < 75;
• SELECT * FROM codemon
WHERE power >= 75;
Hint : Think about what NOT does to the original condition. If NOT (power <= 75), what is the opposite condition? Is 75 included? is 74 ? is 76 ? What's the NOT of that?
Exercise 8 — Combining Multiple Logical Operators
You can combine AND, OR, and NOT to create complex conditions. When combining operators, SQL follows a precedence order:
1. NOT is evaluated first
2. AND is evaluated second
3. OR is evaluated last
Example: Find all Codémons that are fire type OR have both high level (>20) AND high power (>=75):
SELECT * FROM codemon
WHERE type = 'fire' OR level > 20 AND power >= 75;
In this query, AND is evaluated before OR. So it first finds Codémons where level > 20 AND power >= 75, then it adds any fire type Codémons to the results.
You can use parentheses to control the order of evaluation the same way you would in math.
Exercise 9 — Match Operators with Descriptions
Match each logical operator with its correct description.
Items to match
• AND
• OR
• NOT
• ()
Definitions
• All conditions must be true
• At least one condition must be true
• Negates a condition
• Controls the order of evaluation
Exercise 10 — Combining AND, OR, and NOT
Complete the SQL query to find all Codémons that are NOT fire type AND have either a level greater than 15 OR a power less than 80.
SELECT *
FROM codemon
WHERE ____ type = 'fire' ____ (level > 15 ____ power < 80);
Options: NOT AND OR ( ) IS
Hint : Think about the order of operations. NOT applies to the type condition, then we use AND to combine it with the parenthesized expression containing OR.
Exercise 11 — Operator Precedence
Given our Codémon table, which Codémons will the following query return?
SELECT * FROM codemon
WHERE type = 'electric' OR (type = 'water' AND power > 80);
Options
• Only Sparkle
• Sparkle and Aquachu
• Only Aquachu
• Sparkle, Aquachu, and Rockachu
Hint : Remember that AND has higher precedence than OR. The query first evaluates (type = 'water' AND power > 80) and then combines those results with (type = 'electric').