Operators & Conditions

Every WHERE clause we've written so far has leaned on plain equality - a movie's id equals this, a user's username equals that. Real filtering needs a much richer vocabulary than "equals," and MySQL gives you exactly that: comparison operators, logical operators that combine conditions, and a handful of specialized operators built for common shapes of question like "is this value in this range" or "does this text start with a certain word." This lesson works through all of them against StreamHub's movies and reviews tables, including one genuinely important trap around NULL that catches almost everyone at least once.

Comparison Operators

These six operators compare a column against a value (or another column) and are the building blocks of every condition you'll write:

A quick worked example - every movie released after 2020:

SELECT title, release_year
FROM movies
WHERE release_year > 2020;

Combining Conditions: AND, OR, and NOT

A single comparison rarely tells the whole story, so MySQL lets you chain conditions together with AND (both sides must be true), OR (at least one side must be true), and NOT (flips a condition's truth value). The part that trips people up is precedence: MySQL evaluates AND before OR, the same way multiplication is evaluated before addition in arithmetic. When a query mixes both without parentheses, it doesn't always group the way you'd read it in plain English.

Say we want "movies released after 2015 that either scored well or haven't been rated at all." We'll look at release_year from movies alongside rating from reviews - joining those two tables is covered in full two lessons from now, so for the moment just read this as one combined row per review:

SELECT m.title, m.release_year, r.rating
FROM movies m
JOIN reviews r ON r.movie_id = m.id
WHERE m.release_year > 2015 AND r.rating > 4 OR r.rating IS NULL;

Because AND binds tighter, MySQL actually reads this as (m.release_year > 2015 AND r.rating > 4) OR r.rating IS NULL. That second half has nothing to do with the release year at all - any review with a missing rating gets included, even one attached to a movie from 2009. Here's the confusing result that produces:

The Hollow Choir came out in 2009 - it has no business being in a result about movies "released after 2015." It only slipped in because its unrated review satisfied the OR branch on its own. Parentheses fix this by making the grouping explicit:

SELECT m.title, m.release_year, r.rating
FROM movies m
JOIN reviews r ON r.movie_id = m.id
WHERE m.release_year > 2015 AND (r.rating > 4 OR r.rating IS NULL);

BETWEEN ... AND ... for Ranges

BETWEEN checks whether a value falls within a range, inclusive of both endpoints. It reads naturally and saves you from writing two separate comparisons joined by AND. Movies released between 2010 and 2020:

SELECT title, release_year
FROM movies
WHERE release_year BETWEEN 2010 AND 2020;

Note that a movie released in exactly 2010 or exactly 2020 would appear too - BETWEEN is equivalent to release_year >= 2010 AND release_year <= 2020, not a strict "in between."

IN (...) for Matching a List of Values

IN checks a column against a list of acceptable values, replacing what would otherwise be a long chain of OR comparisons. Reviews with a rating of 4 or 5:

SELECT id, movie_id, rating
FROM reviews
WHERE rating IN (4, 5);

That single line replaces WHERE rating = 4 OR rating = 5 - a small difference here, but a very readable one once the list grows to five or ten values.

LIKE and Wildcard Patterns

LIKE matches text against a pattern instead of an exact value, using two wildcard characters: % stands in for any sequence of characters (including none), and _ stands in for exactly one character. Every movie whose title starts with "The ":

SELECT title
FROM movies
WHERE title LIKE 'The %';

% at the end would instead match anywhere the pattern starts - '%Signal%' finds "Signal" anywhere in the title. The single-character wildcard is less common but precise: '_atic' would match a five-letter word ending in "atic" (like "Static"), one character exactly in place of the underscore.

IS NULL and IS NOT NULL - Why "= NULL" Never Works

NULL in SQL doesn't mean zero, an empty string, or false - it means unknown. That distinction has a very concrete consequence: comparing anything to NULL with =, including comparing NULL to NULL, doesn't evaluate to true. It evaluates to unknown, which SQL treats as not-true for the purposes of a WHERE clause - so the row is silently excluded, with no error to warn you. Several of StreamHub's reviews have no comment because a user just left a star rating. This query looks reasonable but returns nothing useful:

SELECT id, rating, comment
FROM reviews
WHERE comment = NULL;

The correct way to test for a missing value is IS NULL (and its opposite, IS NOT NULL):

SELECT id, rating, comment
FROM reviews
WHERE comment IS NULL;

Putting It Together: A Realistic Combined Query

Real questions rarely use just one operator. Suppose the StreamHub team wants to find promising sci-fi-adjacent movies released after 2015 that haven't picked up a single review yet - good candidates to feature and nudge early viewers toward. That needs LIKE (to match the genre name loosely), AND (to combine the year condition), and the "no match at all" pattern of joining to reviews and checking IS NULL - the exact LEFT JOIN technique the Inner & Outer Joins lesson covers in depth:

SELECT DISTINCT m.title, m.release_year
FROM movies m
JOIN movie_genres mg ON mg.movie_id = m.id
JOIN genres g ON g.id = mg.genre_id
LEFT JOIN reviews r ON r.movie_id = m.id
WHERE g.name LIKE '%Sci-Fi%'
  AND m.release_year > 2015
  AND r.id IS NULL;

Every piece from this lesson shows up here at once: LIKE narrows by genre name, AND chains the year condition on top of it, and IS NULL against the joined review's id is what actually detects "no review exists" - because a plain = NULL would, as we just saw, never match anything at all.