Filtering with WHERE

WHERE narrows result sets.

It is the core tool for turning broad table scans into focused answers.

Core Concepts

flowchart LR
  A[All table rows] --> B[Apply WHERE condition]
  B --> C[Only matching rows returned]

Filter type	Example
Equality	WHERE status = 'open'
Numeric comparison	WHERE amount > 100
Combined logic	WHERE status = 'open' AND priority = 'high'

Code Examples

-- Setup issues table for filtering demo.
CREATE TABLE issues_where (
  issue_id  INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  status    TEXT NOT NULL,
  severity  INTEGER NOT NULL
);

INSERT INTO issues_where (title, status, severity)
VALUES
  ('Login bug', 'open', 3),
  ('UI typo', 'closed', 1),
  ('Crash on save', 'open', 5);

Expected output
Table created and three rows inserted.

-- Return only open issues.
SELECT issue_id, title, severity
FROM issues_where
WHERE status = 'open';

issue_id	title	severity
1	Login bug	3
3	Crash on save	5

SQLite-Specific Nuances

SQLite Nuance

SQLite evaluates WHERE expressions per row and returns rows where the condition is true.

Common Pitfalls / Best Practices

Pitfall

Using text values with inconsistent capitalization and expecting exact matches.

Best Practice

Keep status/category values standardized (or normalized) so filters stay simple and reliable.

Quick Challenge

Return issues with severity >= 4.

View Solution

SELECT issue_id, title, severity
FROM issues_where
WHERE severity >= 4;

issue_id	title	severity
3	Crash on save	5