Operators & Conditions

Every WHERE clause you've written so far has quietly leaned on operators — =, >, maybe an AND. This lesson covers the full toolkit SQLite gives you for building conditions: comparison and logical operators, the readability shortcuts BETWEEN and IN, pattern matching with LIKE and SQLite's own GLOB, and the NULL-safe IS / IS NOT comparisons. We'll keep filtering the same RecipeBox tables — recipes, reviews, users, and ingredients — throughout.

Comparison Operators

SQLite supports the standard set of comparison operators, and both != and <> mean "not equal" — they're interchangeable.

-- Recipes that take 30 minutes or less to prepare
SELECT title, prep_minutes
FROM recipes
WHERE prep_minutes <= 30;

-- Reviews that are not a perfect 5
SELECT id, rating
FROM reviews
WHERE rating != 5;   -- same as: rating <> 5

Logical Operators: AND, OR, NOT

Combine conditions with AND, OR, and NOT. As in most SQL dialects, AND binds tighter than OR, so wrap sub-conditions in parentheses when mixing them to make the intent explicit rather than relying on precedence.

-- Quick, highly-rated recipes
SELECT title, prep_minutes, rating
FROM recipes
WHERE prep_minutes <= 20 AND rating >= 4;

-- Vegetarian OR vegan recipes, but never both flagged wrong
SELECT title
FROM recipes
WHERE (is_vegetarian = 1 OR is_vegan = 1)
  AND NOT is_archived = 1;

BETWEEN ... AND

BETWEEN is inclusive on both ends — it's shorthand for two comparisons chained with AND.

-- Recipes rated 4 or 5 (inclusive both ends)
SELECT title, rating
FROM recipes
WHERE rating BETWEEN 4 AND 5;

-- Equivalent, spelled out
SELECT title, rating
FROM recipes
WHERE rating >= 4 AND rating <= 5;

IN (...)

IN replaces a chain of OR-ed equality checks against the same column — and, as later lessons cover, the list can be a subquery instead of a literal list.

-- Recipes in a fixed set of categories
SELECT title, category
FROM recipes
WHERE category IN ('Dessert', 'Breakfast', 'Snack');

-- NOT IN excludes a set
SELECT title, category
FROM recipes
WHERE category NOT IN ('Dessert', 'Snack');

LIKE Pattern Matching

LIKE matches text patterns using two wildcards: % for zero or more of any character, and _ for exactly one character. For ASCII text, SQLite's LIKE is case-insensitive by default.

-- Any title that contains "chicken", anywhere
SELECT title
FROM recipes
WHERE title LIKE '%chicken%';   -- matches "Chicken Soup" too, case-insensitively

-- Titles that start with "Chicken"
SELECT title
FROM recipes
WHERE title LIKE 'Chicken%';

-- Underscore matches exactly one character: "P_e" matches "Pie" but not "Poutine"
SELECT title
FROM recipes
WHERE title LIKE 'P_e';

GLOB Pattern Matching

GLOB is a pattern-matching operator that is genuinely distinct to SQLite (most other SQL databases don't have it). It uses Unix shell-style wildcards instead of SQL's % / _: * matches any run of characters and ? matches exactly one, and it also supports character classes like [abc] or ranges like [a-z]. The critical difference from LIKE: GLOB is always case-sensitive, regardless of the text's collation.

-- GLOB uses *, not %, and is case-sensitive
SELECT title
FROM recipes
WHERE title GLOB 'Chicken*';   -- will NOT match "chicken soup" (lowercase c)

-- Character class: titles starting with an uppercase A, B, or C
SELECT title
FROM recipes
WHERE title GLOB '[ABC]*';

IS / IS NOT NULL

NULL means "no value," and it never equals anything — not even another NULL — so WHERE column = NULL silently matches nothing. Use IS and IS NOT for NULL-safe comparisons instead.

-- Reviews left without a written comment
SELECT id, rating
FROM reviews
WHERE comment IS NULL;

-- Recipes that do have a category set
SELECT title
FROM recipes
WHERE category IS NOT NULL;