Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data Conditions with WHERE
This device
Course contentsConditions with WHERE · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONYour First SELECT Query
NEXT LESSONSorting, Limiting, and Distinct Values
Reading and filtering data · Lesson 06 125 min

Conditions with WHERE

The previous lesson chose which columns to return. WHERE chooses which rows qualify. A useful filter is a clear statement about the data: published courses, affordable courses, or courses that satisfy several rules at once.

What you will leave with

You will be able to filter with comparisons, combine predicates with AND, OR, and NOT, use IN and inclusive BETWEEN ranges, group mixed logic with parentheses, test missing values with IS NULL, and predict which rows a query will return before running it.

WHERE keeps rows only when its condition is true

Filter rowsKeep only source rows whose predicate evaluates to true.
Compare valuesTest equality, inequality, and numeric boundaries.
Group logicMake mixed AND and OR conditions express the intended rule.
Handle unknownsUse IS NULL when a missing value should be included or excluded.
SOURCEcourses8 stored rows

Each row has a title, category, status, duration, price, and optional rating.

PREDICATEstatus = 'published'WHERE condition

The database tests this condition for each candidate row.

RESULT6 matching rowsSELECT title, status

Draft courses are excluded; the stored table remains unchanged.

Put the condition after FROM

SELECT course_id, title, status FROM courses WHERE status = 'published'; requests three columns from rows whose status equals the text value 'published'. The seed has eight courses: six published and two draft. A query without WHERE would return all eight. Adding this condition does not delete or modify the draft rows.

SQL BROWSER RUNNER

Keep published courses

Run a basic equality condition and compare the six result rows with the eight seeded rows.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

SELECT course_id, title, status
FROM courses
WHERE status = 'published';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change published to draft. Predict the two matching course IDs before running again.

Comparisons describe boundaries precisely

Use = for equality and <> for inequality. For numbers and dates, <, <=, >, and >= express boundaries. Price is stored in cents, so price_cents <= 2000 means at most 20 dollars, including course 104 at exactly 2000 cents. A text comparison depends on the database collation; do not assume it will ignore capitalization.

SQL BROWSER RUNNER

Filter by price

Return courses priced at 2,000 cents or less, including free courses.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

SELECT course_id, title, price_cents
FROM courses
WHERE price_cents <= 2000;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Replace <= with <, then compare the result. Change the boundary to 1200 and test whether Accessible UI stays in the result.

Check the unit before comparing

The column is price_cents, not dollars. A condition like price_cents <= 20 would mean twenty cents, not twenty dollars. Good names keep a filter from being syntactically valid but logically wrong.

AND, OR, and NOT combine conditions

AND requires both conditions to be true. OR accepts a row when either condition is true. NOT reverses a condition. In the example, a course must be published and must not be in the Backend category. That leaves published Data and Web courses. The parentheses make the part being negated explicit.

SQL BROWSER RUNNER

Combine status and category rules

Keep published courses while excluding the Backend category.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

SELECT course_id, title, category, status
FROM courses
WHERE status = 'published'
  AND NOT (category = 'Backend');
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Replace AND with OR. Which draft rows now appear, and why? Restore AND before moving on.

Parentheses make mixed logic reviewable

SQL evaluates NOT before AND, and AND before OR. The first query below therefore means (published AND Data) OR Web. It includes the draft JavaScript DOM course because every Web row satisfies the second branch. The second query means published AND (Data OR Web), which keeps the publication rule for both categories.

SQL BROWSER RUNNER

See how grouping changes the answer

Compare an ungrouped AND/OR filter with the intended parenthesized version.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

-- Compare this result with the parenthesized version below.
SELECT course_id, title, category, status
FROM courses
WHERE status = 'published' AND category = 'Data'
   OR category = 'Web';

SELECT course_id, title, category, status
FROM courses
WHERE status = 'published'
  AND (category = 'Data' OR category = 'Web');
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Find course 104 in the first result and verify that it is absent from the second. Rewrite the first query so both results agree.

The runner prints both result sets

When the script has more than one SELECT, the output panel prints each result in statement order. Comment out one query if you want to focus on the other. Neither result has a guaranteed row order without ORDER BY.

IN tests membership in a short set

category IN ('Data', 'Web') asks whether the category equals either listed value. It is clearer than repeating the same column in a long OR chain. The example also uses status <> 'draft' to exclude drafts. Use NOT IN carefully when the list or subquery might contain NULL: unknown values can make the result surprising. For a known, short list of non-null constants, IN is straightforward.

SQL BROWSER RUNNER

Filter categories with IN

Return published Data or Web courses with a compact membership test.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

SELECT course_id, title, category
FROM courses
WHERE category IN ('Data', 'Web')
  AND status <> 'draft';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add Backend to the IN list, then use NOT IN ('Backend') to express the original category choice.

BETWEEN includes both endpoints

duration_minutes BETWEEN 90 AND 120 is equivalent to duration_minutes >= 90 AND duration_minutes <= 120. It includes courses of exactly 90 and exactly 120 minutes. The lower endpoint comes first. Use explicit comparisons when one boundary should be excluded, such as durations greater than 90 but at most 120.

SQL BROWSER RUNNER

Test an inclusive duration range

Find courses whose durations fall between 90 and 120 minutes, including both endpoints.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

SELECT course_id, title, duration_minutes
FROM courses
WHERE duration_minutes BETWEEN 90 AND 120;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Rewrite BETWEEN with >= and <=. Then make the lower boundary exclusive and check which row disappears.

Unknown is not the same as false

The rating column allows NULL while a course waits for reviews. A comparison such as rating = NULL does not return the unrated courses; it evaluates to unknown. WHERE keeps only rows for which the condition is true, so both false and unknown are excluded. Use IS NULL to find missing ratings and IS NOT NULL to require one.

SQL BROWSER RUNNER

Find unrated and highly rated courses

Compare IS NULL with a non-null rating threshold in two result sets.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

SELECT course_id, title, rating
FROM courses
WHERE rating IS NULL;

SELECT course_id, title, rating
FROM courses
WHERE rating IS NOT NULL AND rating >= 4.5;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Replace IS NULL in the first query with = NULL and observe the empty result. Restore IS NULL, then add status = 'published' to exclude unrated drafts.

True

The row is returned by WHERE.

False

The row is filtered out.

Unknown

A comparison involving NULL is also filtered out unless you use an explicit null test.

Common filtering mistakes

status = published

Unquoted text

Quote text values. Without quotes, SQL reads published as an identifier, usually a column name.

published AND Data OR Web

Missing grouping

Parenthesize category alternatives so the publication rule applies to every accepted category.

rating = NULL

Missing-value comparison

Use rating IS NULL or rating IS NOT NULL to test absence.

BETWEEN 120 AND 90

Reversed range

Put the lower endpoint first and remember that both endpoints are included.

Independent lab: curate a course catalog

Build a feed for affordable published Data or Web courses. A course qualifies when it costs at most 2,000 cents and either has a rating of at least 4.5 or has no rating yet. The starter query should return only course IDs 101 and 107. Remove the parentheses around the last OR condition, predict what leaks in, and then repair the rule. Finally, rewrite the category membership test as a parenthesized OR group.

SQL BROWSER RUNNER

Write a reliable catalog filter

Combine status, category, price, and optional rating into one reviewable condition.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4);

-- Curate affordable published Data or Web courses.
-- Include high-rated courses and published courses awaiting a rating.
SELECT course_id, title, category, price_cents, rating
FROM courses
WHERE status = 'published'
  AND category IN ('Data', 'Web')
  AND price_cents BETWEEN 0 AND 2000
  AND (rating >= 4.5 OR rating IS NULL);
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Prove that IDs 101 and 107 qualify. Remove and restore the final parentheses, then rewrite IN as (category = 'Data' OR category = 'Web').

Lab review criteria

The final query returns the same two course IDs even when you reorder its conditions. Draft courses never qualify. The 2,000-cent boundary is inclusive. The missing rating is accepted only for a course that passes the other rules. The stored table still has eight rows after every run.

Lesson review

WHERE filters source rows before the chosen columns are returned. Comparisons test values and boundaries. AND, OR, and NOT express combined rules, while parentheses make mixed logic explicit. IN checks membership, BETWEEN includes both endpoints, and IS NULL tests missing values. The most reliable habit is to state the rule in words, predict a few matching and nonmatching rows, then run the query and inspect the result.

  • I can distinguish filtering rows with WHERE from projecting columns with SELECT.
  • I can choose the correct equality, inequality, and boundary operator.
  • I can group AND and OR logic so every branch follows the intended rule.
  • I know that BETWEEN includes both endpoints.
  • I can use IN for a known set of values and IS NULL for missing data.
  • I can predict matching rows and check a query against counterexamples.
KNOWLEDGE CHECK

Check your filtering logic

Answer all nine questions, then use the explanations and runnable examples to revisit any condition you missed.

01What does WHERE change in a SELECT query?
02Which condition includes a course priced at exactly 2,000 cents?
03Which condition requires a published course to be in either Data or Web?
04What does NOT (category = 'Backend') mean?
05What does category IN ('Data', 'Web') test?
06Which durations match duration_minutes BETWEEN 90 AND 120?
07Which condition finds rows where rating is missing?
08What happens when a WHERE condition evaluates to unknown?
09Why predict matching and nonmatching rows before running a complex filter?
PREVIOUS LESSONYour First SELECT Query
NEXT LESSONSorting, Limiting, and Distinct Values
ON THIS PAGEConditions with WHERELesson mapFirst filterComparisonsAND, OR, NOTParenthesesINBETWEENNULL checksCommon mistakesIndependent labLesson reviewKnowledge check
Course contents