0Pricing
SQL Academy · Lesson

WHERE Filters: Comparison and Logic Operators

Filter rows with =, <>, , AND, OR, NOT and understand operator precedence and parentheses.

WHERE Filters: Comparison and Logic Operators is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Role of WHERE

WHERE filters rows before grouping or aggregation. Only rows where the predicate is TRUE are returned.

Comparison Operators

Standard comparisons work on all comparable types:

SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price <= 50;
SELECT * FROM products WHERE category = 'books';
SELECT * FROM products WHERE category <> 'books';   -- != also works

AND, OR, NOT

Combine predicates with logical operators:

-- Both true:
SELECT * FROM products
WHERE category = 'books' AND price < 50;

-- Either true:
SELECT * FROM products
WHERE category = 'books' OR category = 'music';

-- Negation:
SELECT * FROM products
WHERE NOT (category = 'books');

Operator Precedence

Precedence (high to low): NOT → AND → OR. Always parenthesise complex predicates for clarity:

-- Without parens — almost certainly wrong:
SELECT * FROM orders
WHERE status = 'open' OR status = 'pending' AND total > 100;
-- means:  status = open  OR  (status = pending AND total > 100)

-- With parens — clearer:
SELECT * FROM orders
WHERE (status = 'open' OR status = 'pending') AND total > 100;

IN and NOT IN

Shortcut for many ORs:

SELECT * FROM users
WHERE country IN ('US', 'CA', 'GB', 'AU');

-- Equivalent to:
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' OR country = 'GB' OR country = 'AU';

BETWEEN

Inclusive range check:

SELECT * FROM orders
WHERE total BETWEEN 100 AND 500;

-- Equivalent to:  total >= 100 AND total <= 500

NULL-Safe Filters

Comparisons with NULL yield NULL (not FALSE), and rows with NULL are dropped by WHERE. Use IS NULL / IS NOT NULL explicitly when needed:

-- Returns users where deleted_at is unknown:
SELECT * FROM users WHERE deleted_at IS NULL;

-- IS DISTINCT FROM treats NULL as a comparable value:
SELECT * FROM events WHERE status IS DISTINCT FROM 'closed';

Boolean Columns

A BOOLEAN column needs no comparison:

SELECT * FROM tasks WHERE done;       -- = TRUE
SELECT * FROM tasks WHERE NOT done;    -- = FALSE or NULL
SELECT * FROM tasks WHERE done IS NOT TRUE;  -- FALSE or NULL only

Pattern Matching with LIKE

% matches any string, _ matches exactly one character:

SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE sku LIKE 'A_-___';

-- Case-insensitive in PostgreSQL:
SELECT * FROM users WHERE email ILIKE '%@GMAIL.com';

Filters and Performance

WHERE clauses are where indexes earn their keep. Equality (=) and range (<, BETWEEN) filters on indexed columns turn full table scans into millisecond lookups.

Order of Evaluation

Logically the database evaluates: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. So WHERE sees raw rows; column aliases from SELECT are not yet defined.

-- This fails — alias not visible in WHERE:
SELECT total * 0.9 AS discounted
FROM orders WHERE discounted > 100;     -- ERROR

-- Repeat the expression instead, or wrap in a subquery.

Recap

WHERE is your primary tool for selecting rows.

  • Standard comparison and logical operators
  • Parenthesise mixed AND/OR
  • Use IS NULL — never = NULL
  • WHERE on indexed columns is fast

Quick Check

Which predicate keeps only rows where age is greater than or equal to 18 and country is US?

Frequently asked questions

Is the “WHERE Filters: Comparison and Logic Operators” lesson free?

Yes — the full text of “WHERE Filters: Comparison and Logic Operators” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “WHERE Filters: Comparison and Logic Operators”?

Filter rows with =, <>, , AND, OR, NOT and understand operator precedence and parentheses. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “WHERE Filters: Comparison and Logic Operators” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Selecting Columns and Expressions
  2. WHERE Filters: Comparison and Logic Operators
  3. ORDER BY Single and Multiple Columns
  4. LIMIT, OFFSET and Pagination Basics
← Back to SQL Academy