WHERE Advanced

Overview

Advanced filtering with LIKE, IN, BETWEEN, IS NULL, and compound conditions. This lesson is part of the "SELECT Queries" section and takes approximately 80 minutes to complete.

🎯Learning Objectives

• Combine multiple WHERE conditions using AND, OR, and NOT with correct precedence
• Use parentheses to control evaluation order in compound conditions
• Match string patterns with LIKE and ILIKE
• Filter rows within a numeric or date range using BETWEEN
• Test set membership with IN and NOT IN
• Identify and handle NULL values using IS NULL, IS NOT NULL, and COALESCE
• Explain why NULL = NULL is always NULL (not TRUE) in SQL

📖Theory

1. Compound Conditions: AND, OR, NOT

A WHERE clause can combine multiple conditions using logical operators. The database evaluates each condition for every row and returns only those where the overall result is TRUE.

-- Films that are rated 'G' AND shorter than 60 minutes
SELECT title, rating, length
FROM film
WHERE rating = 'G' AND length < 60;

-- Films rated 'G' OR with a rental rate of exactly $0.99
SELECT title, rating, rental_rate
FROM film
WHERE rating = 'G' OR rental_rate = 0.99;

2. Operator Precedence and Parentheses

AND binds more tightly than OR — exactly like * binds more tightly than + in arithmetic. This means A OR B AND C evaluates as A OR (B AND C), which is frequently not what was intended.

-- BUG: developer wanted (R or NC-17) AND long films,
-- but AND has higher precedence, so this actually means:
-- "all R films" OR "(NC-17 films AND length > 150)"
SELECT title, rating, length
FROM film
WHERE rating = 'R' OR rating = 'NC-17' AND length > 150;

-- CORRECT: use parentheses to make intent explicit
SELECT title, rating, length
FROM film
WHERE (rating = 'R' OR rating = 'NC-17') AND length > 150;

NOT negates the condition that follows it:

-- Films that are NOT rated 'G'
SELECT title, rating
FROM film
WHERE NOT rating = 'G';

-- Equivalent and often more readable
SELECT title, rating
FROM film
WHERE rating <> 'G';

3. LIKE and ILIKE for Pattern Matching

LIKE checks whether a string matches a pattern. Two wildcard characters are available:

-- Films whose title starts with the letter 'A'
SELECT title FROM film WHERE title LIKE 'A%';

-- Films whose title ends with 'ion'
SELECT title FROM film WHERE title LIKE '%ion';

-- Films whose title contains 'love' anywhere
SELECT title FROM film WHERE title LIKE '%love%';

-- Films with exactly a 5-character title
SELECT title FROM film WHERE title LIKE '_____';

LIKE is case-sensitive in PostgreSQL. Use ILIKE for case-insensitive matching:

-- Matches 'HARRIS', 'Harris', 'harris', 'hArRiS', etc.
SELECT first_name, last_name
FROM actor
WHERE last_name ILIKE '%son%';

4. BETWEEN for Range Filtering

BETWEEN low AND high is a shorthand for col >= low AND col <= high. Both endpoints are inclusive.

-- Films with rental_rate between $2 and $4 (inclusive on both ends)
SELECT title, rental_rate
FROM film
WHERE rental_rate BETWEEN 2.00 AND 4.00;

-- Films between 90 and 120 minutes long
SELECT title, length, rating
FROM film
WHERE length BETWEEN 90 AND 120
ORDER BY length ASC;

NOT BETWEEN excludes the range:

-- Films shorter than 60 or longer than 150 minutes
SELECT title, length
FROM film
WHERE length NOT BETWEEN 60 AND 150;

5. IN for Set Membership

IN tests whether a value matches any element in a list. It is more readable than chaining multiple OR conditions.

-- Verbose with OR:
SELECT title, rating FROM film
WHERE rating = 'PG' OR rating = 'PG-13' OR rating = 'G';

-- Clean with IN:
SELECT title, rating FROM film
WHERE rating IN ('PG', 'PG-13', 'G');

NOT IN excludes matching values:

-- Films that are NOT rated 'R' or 'NC-17'
SELECT title, rating
FROM film
WHERE rating NOT IN ('R', 'NC-17');

6. IS NULL and IS NOT NULL

NULL in SQL means "unknown" or "no value". It is not zero, not an empty string — it is the absence of a value. Because NULL represents the unknown, comparing it with = always yields NULL, never TRUE.

-- WRONG: this always returns 0 rows, even when NULL values exist
SELECT * FROM customer WHERE email = NULL;

-- CORRECT: use IS NULL
SELECT first_name, last_name, email
FROM customer
WHERE email IS NULL;

-- Rows that DO have an email address
SELECT first_name, last_name, email
FROM customer
WHERE email IS NOT NULL;

7. COALESCE — Substituting a Default for NULL

COALESCE(expr1, expr2, …) returns the first non-NULL value in its argument list. It is the standard way to substitute a default whenever a column might be NULL.

-- Replace NULL descriptions with a placeholder string
SELECT title,
       COALESCE(description, 'No description available') AS description
FROM film;

-- Use 0 as a default if a numeric column is NULL
SELECT customer_id,
       COALESCE(store_id, 0) AS store_id
FROM customer;

COALESCE accepts more than two arguments — it returns the first non-NULL value found:

-- Try email first, then fall back to 'Unknown contact'
SELECT customer_id,
       COALESCE(email, 'Unknown contact') AS contact_info
FROM customer;

8. NULLIF — Converting a Sentinel Value into NULL

NULLIF(a, b) returns NULL when a equals b, and returns a otherwise. It is useful when a column stores a sentinel value (like 0 or '') that should actually be treated as absent.

-- Treat rental_duration = 0 as unknown
SELECT title,
       NULLIF(rental_duration, 0) AS rental_duration_or_null
FROM film;

A classic pattern: avoid division-by-zero errors:

-- Safe division: if length is 0, result is NULL rather than an error
SELECT title,
       rental_rate,
       ROUND(rental_rate / NULLIF(length, 0) * 100, 4) AS cents_per_min
FROM film;

💻Code Examples

Example A — Combined Rating and Length Filter

-- Find films suitable for a family movie night:
-- rated PG or G, between 80 and 110 minutes, rental rate at most $2.99,
-- sorted by length so the shortest options appear first.

SELECT title,
       rating,
       length,
       rental_rate
FROM film
WHERE rating IN ('PG', 'G')
  AND length BETWEEN 80 AND 110
  AND rental_rate <= 2.99
ORDER BY length ASC, title ASC;

What to notice:

• IN ('PG', 'G') replaces two OR conditions.
• BETWEEN 80 AND 110 replaces length >= 80 AND length <= 110.
• All three conditions are joined with AND — a row must satisfy every one of them to appear.

Example B — Actor Name Search with ILIKE

-- Find all actors whose last name contains 'son' (case-insensitive).
-- Show full name and the character count of the last name.

SELECT first_name,
       last_name,
       length(last_name)  AS last_name_length
FROM actor
WHERE last_name ILIKE '%son%'
ORDER BY last_name ASC, first_name ASC;

What to notice:

• ILIKE '%son%' matches JOHNSON, JOHANSSON, WILSON, and MASON regardless of capitalisation.
• length() is a built-in string function used as an expression in the SELECT list.
• The result columns include both the original last_name and a computed value — both are valid.

Example C — NULL Handling and COALESCE Combined

-- Show R and NC-17 films with long rental durations.
-- Replace NULL descriptions with a placeholder.
-- Compute a safe cost-per-minute that avoids division by zero.

SELECT title,
       rating,
       COALESCE(description, '(no description on file)')   AS description,
       ROUND(rental_rate / NULLIF(length, 0) * 100, 4)     AS cents_per_min
FROM film
WHERE (rating = 'R' OR rating = 'NC-17')
  AND rental_duration > 5
  AND length IS NOT NULL
ORDER BY cents_per_min ASC NULLS LAST;

What to notice:

• The OR inside WHERE is wrapped in parentheses so that AND rental_duration > 5 applies to both ratings.
• IS NOT NULL in WHERE ensures we don't even process rows where length is absent.
• NULLIF(length, 0) is a secondary guard in case length = 0 slipped through.
• NULLS LAST pushes any remaining NULL results to the bottom of the sorted output.

✏️Practice Tasks

SELECT title, length, rental_rate
FROM film
WHERE rating = 'R' AND length > 120
ORDER BY length DESC;

-- Query 1: customers with that domain
SELECT customer_id, first_name, last_name, email
FROM customer
WHERE email ILIKE '%sakilacustomer.org%'
ORDER BY last_name ASC;

-- Query 2: customers with no email
SELECT COUNT(*)
FROM customer
WHERE email IS NULL;

SELECT title,
       rating,
       rental_rate,
       rental_duration,
       left(description, 60) || '...'  AS short_desc
FROM film
WHERE rating IN ('G', 'PG', 'PG-13')
  AND rental_duration BETWEEN 3 AND 6
  AND rental_rate NOT BETWEEN 2.00 AND 4.00
  AND description IS NOT NULL
  AND description ILIKE '%Drama%'
ORDER BY rental_rate ASC;

⚠️Common Mistakes

Using = NULL instead of IS NULL

WHERE email = NULL always returns zero rows because comparing anything to NULL produces NULL, not TRUE. PostgreSQL does not raise an error — it just silently returns an empty result set. Always write WHERE email IS NULL.

Forgetting parentheses when mixing AND and OR

WHERE rating = 'R' OR rating = 'NC-17' AND length > 120 evaluates as WHERE rating = 'R' OR (rating = 'NC-17' AND length > 120) due to precedence. This returns all R-rated films regardless of length — almost certainly not the intent. Parentheses are the only safe fix.

NOT IN with a NULL in the list

WHERE rating NOT IN ('R', NULL) returns no rows. SQL expands it to rating <> 'R' AND rating <> NULL. Since rating <> NULL is NULL, the whole condition is NULL for every single row. Never include a NULL value in a NOT IN list.

LIKE without wildcards

WHERE title LIKE 'Alien' (no %) is identical to WHERE title = 'Alien'. It only matches the exact string. Add % wildcards if you intend a partial match: WHERE title LIKE '%Alien%'.

BETWEEN is always inclusive on both ends

BETWEEN 1 AND 5 includes both 1 and 5. If you need a half-open range ("at least 1, strictly less than 5"), use explicit >= 1 AND < 5 comparisons instead.

ILIKE with a leading wildcard cannot use a standard index

ILIKE '%son%' forces a full table scan because the index cannot be used when the pattern starts with %. On small tables like DVD Rental's 200 actors, this is fine. On large production tables, consider a trigram index (pg_trgm extension) or full-text search.

🎓Instructor Notes

How to run this lesson (~80 min)

• [10 min] Recap and motivation. Ask: "In lesson 26 we used WHERE rating = 'PG'. What if you want PG or PG-13? What if you want films between 90 and 120 minutes?" Let students attempt to write the query first — most will use OR correctly but forget parentheses when combining it with AND.
• [20 min] Live code: AND/OR/NOT and parentheses. Run the buggy precedence example from section 2 and show the wrong result count. Fix it with parentheses and show the correct count. This demonstration tends to stick in memory for the rest of the course.
• [15 min] Live code: LIKE, ILIKE, BETWEEN, IN. Do each operator once live on DVD Rental. For LIKE, show all four wildcard patterns. Ask students to predict the row count before running each query — this keeps them mentally engaged.
• [10 min] Live code: IS NULL, COALESCE, NULLIF. The NULL concept is consistently confusing. Run WHERE email = NULL first, show it returns 0 rows, then switch to IS NULL. For COALESCE, use the description column — students can immediately see which rows would have been blank.
• [20 min] Task 1 in class. Most students finish Task 1 in 5 minutes. Use the extra time for a quick group challenge: "Can you write a query to find films whose title does NOT start with 'The'?" (Answer: WHERE title NOT LIKE 'The %'.)
• [5 min] Assign homework. Tasks 2 and 3 go home. Encourage breaking Task 3 into pieces — write each condition separately, verify it works alone, then combine everything into one query.

Discussion questions

• "If NULL means 'unknown', why does NULL = NULL return NULL instead of TRUE? Two unknown things could be equal — right?" (Discussion answer: two unknowns might be the same or different things, so the result is also unknown.)
• "When would you choose IN over multiple OR conditions? Is there ever a functional difference?"
• "Can you think of a real-world table where most rows would have NULL in one column? How would COALESCE improve the queries on that table?"