Filtering with WHERE & Boolean Logic — SQL Course

What you'll learn

Filter rows with comparisons, ranges, pattern matching, membership tests, and correctly grouped Boolean conditions. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Comparison operators to a realistic data question
Apply AND OR NOT to a realistic data question
Apply BETWEEN to a realistic data question
Apply IN to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Predicate	An expression evaluated as true, false, or unknown	Translate each business rule into a separately testable condition
Precedence	The order operators are evaluated	Parenthesize mixed AND and OR logic to show intent
Pattern	A text-matching rule such as LIKE	Escape or parameterize user input and know whether case matters

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the row-filter predicate question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Filter with explicit grouping

Parentheses preserve the requested business meaning and the half-open date range avoids time-of-day bugs.

filter_books.sql

SELECT id, title, category, price
FROM course.books
WHERE published_at >= DATE '2025-01-01'
  AND published_at < DATE '2026-01-01'
  AND (category IN ('Database', 'Backend') OR title ILIKE '%sql%')
  AND price BETWEEN 20 AND 60;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

WHERE a OR b AND c means a OR (b AND c), which may return far more rows than the English requirement.

Independent workshop

Build a review-ready row-filter predicate lab against the course commerce dataset.

Your finished workshop must include:

Comparison operators
AND OR NOT
BETWEEN
IN
LIKE and ILIKE
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Predicate: Translate each business rule into a separately testable condition
Precedence: Parenthesize mixed AND and OR logic to show intent
Pattern: Escape or parameterize user input and know whether case matters

Quick check

1. Which rule best applies to Predicate?

2. Which rule best applies to Precedence?

3. Which rule best applies to Pattern?

Next: NULL & Three-Valued Logic