SQLite WHERE

The WHERE clause filters rows. Without it, SELECT returns every row in the table. With it, you retrieve only the rows that match your condition. WHERE works with SELECT, UPDATE, and DELETE statements.

Sample Table

employees
────────────────────────────────────────────────────────
id │ name    │ dept          │ salary  │ active
───┼─────────┼───────────────┼─────────┼────────
1  │ Alice   │ Engineering   │ 85000   │ 1
2  │ Bob     │ Marketing     │ 62000   │ 1
3  │ Carol   │ Engineering   │ 91000   │ 0
4  │ Dan     │ HR            │ 55000   │ 1
5  │ Eve     │ Marketing     │ 70000   │ 1
6  │ Frank   │ HR            │ 52000   │ 0

Basic WHERE Syntax

SELECT column_list
FROM table_name
WHERE condition;

Comparison Operators

OPERATOR   MEANING           EXAMPLE
─────────────────────────────────────────────────
=          equals            WHERE dept = 'HR'
!=  or <>  not equals        WHERE dept != 'HR'
<          less than         WHERE salary < 60000
<=         less or equal     WHERE salary <= 60000
>          greater than      WHERE salary > 70000
>=         greater or equal  WHERE salary >= 85000

Single Condition Examples

-- Only Engineering employees
SELECT name, salary FROM employees
WHERE dept = 'Engineering';

name  │ salary
──────┼────────
Alice │ 85000
Carol │ 91000

-- Salary above 70000
SELECT name, salary FROM employees
WHERE salary > 70000;

name  │ salary
──────┼────────
Alice │ 85000
Carol │ 91000

AND Operator — Both Conditions Must Be True

-- Engineering employees who are active
SELECT name FROM employees
WHERE dept = 'Engineering' AND active = 1;

name
─────
Alice

OR Operator — At Least One Condition Must Be True

-- Marketing OR HR employees
SELECT name, dept FROM employees
WHERE dept = 'Marketing' OR dept = 'HR';

name  │ dept
──────┼──────────
Bob   │ Marketing
Dan   │ HR
Eve   │ Marketing
Frank │ HR

NOT Operator — Invert a Condition

-- Everyone except Engineering
SELECT name, dept FROM employees
WHERE NOT dept = 'Engineering';

name  │ dept
──────┼──────────
Bob   │ Marketing
Dan   │ HR
Eve   │ Marketing
Frank │ HR

Combining AND and OR (Use Parentheses)

Without parentheses, AND evaluates before OR. Use parentheses to make your intent explicit.

-- Engineering OR Marketing, but only active employees
SELECT name, dept FROM employees
WHERE (dept = 'Engineering' OR dept = 'Marketing')
  AND active = 1;

name  │ dept
──────┼──────────
Alice │ Engineering
Bob   │ Marketing
Eve   │ Marketing

WHERE with NULL

You cannot use = NULL to find missing values. NULL comparisons require IS NULL or IS NOT NULL.

-- WRONG way to find NULLs:
SELECT * FROM employees WHERE salary = NULL;    ← returns nothing

-- CORRECT way:
SELECT * FROM employees WHERE salary IS NULL;

-- Find rows where salary IS set:
SELECT * FROM employees WHERE salary IS NOT NULL;

WHERE in UPDATE and DELETE

-- Update only Carol's active status
UPDATE employees SET active = 1 WHERE name = 'Carol';

-- Delete only inactive employees
DELETE FROM employees WHERE active = 0;

-- Without WHERE — DANGER:
DELETE FROM employees;     ← deletes ALL rows
UPDATE employees SET salary = 0;  ← zeros out ALL rows

WHERE Condition Decision Tree

Each row in table
       │
       ▼
Does row match WHERE condition?
       │
  ┌────┴─────┐
  Yes        No
  │          │
  ▼          ▼
Include    Skip
in result  this row

Practical Example: Finding Employees to Promote

-- Find active employees in Engineering earning under $90,000
SELECT id, name, salary
FROM employees
WHERE dept = 'Engineering'
  AND active = 1
  AND salary < 90000;

id │ name  │ salary
───┼───────┼────────
1  │ Alice │ 85000

Key Takeaways

• WHERE filters rows based on one or more conditions
• AND requires all conditions to be true; OR requires at least one
• Use parentheses when mixing AND and OR to control evaluation order
• Use IS NULL and IS NOT NULL to check for missing values
• WHERE without a condition in DELETE or UPDATE affects every row — always double-check