Filtering Rows: WHERE, AND, OR and NOT

WHERE decides which rows a query keeps. It is the most used clause in SQL after SELECT and FROM, and in data work it is usually the most important one: "only delivered orders", "only customers from Dhaka", "only the API calls that failed yesterday", "only labelled examples for the training set".

Filtering in the database, not in Python after loading everything, is also a habit worth building now. A table of 50 million log rows will not fit in a pandas DataFrame on your laptop; the 2,000 rows your WHERE picks out will.

What you will learn

• Keep rows with WHERE and the comparison operators =, <>, <, <=, >, >=.
• How text, numbers and dates compare, and where the three databases differ.
• Combine conditions with AND, OR and NOT.
• Operator precedence, and the classic AND/OR bug that parentheses fix.
• Pass filter values from Python safely.

WHERE keeps the rows where the condition is true

SELECT name, price
FROM products
WHERE price < 2000
ORDER BY price;

+---------------------+-------+
| name                | price |
+---------------------+-------+
| Wireless Mouse      |   900 |
| Gift Card           |  1000 |
| Python Crash Course |  1200 |
| Laptop Stand        |  1500 |
+---------------------+-------+

The database checks the condition price < 2000 for every row of products. Rows where it is true are kept; all others are dropped before anything else happens. In the logical order of a query, WHERE runs right after FROM:

FROM products           take all 11 rows
→ WHERE price < 2000    keep the 4 that pass
→ SELECT name, price    choose the columns
→ ORDER BY price        sort what is left
→ LIMIT                 cut (if asked)

Two things follow. First, WHERE can test a column you do not select. Second, WHERE cannot see aliases made in SELECT, because SELECT has not run yet (more on that under Common mistakes).

Comparison operators

A comparison is itself a value: true or false. SQLite and MySQL show true as 1 and false as 0; PostgreSQL shows true and false. You can select one to see it:

SELECT name, stock, stock <= 5 AS low_stock
FROM products
WHERE category_id = 2
ORDER BY product_id;

+-----------------------------+-------+-----------+
| name                        | stock | low_stock |
+-----------------------------+-------+-----------+
| Mechanical Keyboard         |    12 |         0 |
| Wireless Mouse              |    60 |         0 |
| 27-inch Monitor             |     5 |         1 |
| Noise-Cancelling Headphones |     8 |         0 |
+-----------------------------+-------+-----------+

Text, numbers and dates

Write numbers bare (2000) and text and dates in single quotes ('Dhaka', '2026-03-01'). How values compare depends on their type:

• Numbers compare by value: 900 < 2000.
• Text compares character by character, like words in a dictionary, but by character code: digits before uppercase before lowercase. So '9' < '10' is false (the first characters '9' and '1' decide), and 'Zebra' < 'apple' is true.
• Dates in SQLite are text in ISO 8601 form ('YYYY-MM-DD'). That form was designed so text order equals time order, which is why order_date >= '2026-04-01' works. A date written '01/04/2026' would not compare correctly.

SELECT 9 < 10           AS numbers,
       '9' < '10'       AS texts,
       'Zebra' < 'apple' AS by_code,
       'Dhaka' = 'dhaka' AS same_case;

+---------+-------+---------+-----------+
| numbers | texts | by_code | same_case |
+---------+-------+---------+-----------+
|       1 |     0 |       1 |         0 |
+---------+-------+---------+-----------+

The last column shows that in SQLite = on text is case-sensitive: WHERE city = 'dhaka' finds nobody. PostgreSQL behaves the same. MySQL does not: its default collation (the rules for comparing text, here utf8mb4_0900_ai_ci, where ci means case-insensitive) treats them as equal:

-- MySQL
SELECT name
FROM customers
WHERE city = 'dhaka'
ORDER BY customer_id;

+---------------+
| name          |
+---------------+
| Nadia Rahman  |
| Farhana Akter |
| Mitu Das      |
+---------------+

The same query returns three rows on MySQL and none on SQLite or PostgreSQL. When you move a query between databases, or when an LLM writes SQL for you, check text comparisons first. (Case-insensitive search the portable way, with LOWER(), is on the next page.)

When text meets a number

Comparing a number with text is where dialects disagree most. In SQLite, the price column is declared INTEGER, so SQLite converts '2000' to a number before comparing, and price > '2000' happens to work. PostgreSQL also accepts a quoted number for an integer column, but stops you when the text is not a number:

-- PostgreSQL
SELECT name
FROM products
WHERE price > 'cheap';

Error: invalid input syntax for type integer: "cheap"

MySQL quietly converts 'cheap' to 0 (with only a warning) and returns every product. An error is better than a wrong answer. The safe habit everywhere: compare numbers with numbers, without quotes, and text with quoted text.

AND, OR and NOT

Real filters have several conditions. AND needs both sides true, OR needs at least one, NOT flips true and false.

-- customers in Dhaka who joined in 2026
SELECT name, joined_on
FROM customers
WHERE city = 'Dhaka' AND joined_on >= '2026-01-01'
ORDER BY customer_id;

+---------------+------------+
| name          | joined_on  |
+---------------+------------+
| Farhana Akter | 2026-01-09 |
| Mitu Das      | 2026-03-30 |
+---------------+------------+

-- every order that is not finished yet
SELECT order_id, status
FROM orders
WHERE status = 'shipped' OR status = 'pending'
ORDER BY order_id;

+----------+---------+
| order_id | status  |
+----------+---------+
|       12 | shipped |
|       13 | pending |
+----------+---------+

-- every order that was not delivered
SELECT order_id, status
FROM orders
WHERE NOT status = 'delivered'
ORDER BY order_id;

+----------+-----------+
| order_id | status    |
+----------+-----------+
|        5 | cancelled |
|       12 | shipped   |
|       13 | pending   |
+----------+-----------+

NOT status = 'delivered' means the same as status <> 'delivered'; use whichever reads better. NOT earns its place in front of a group, as in NOT (a OR b).

Precedence: the classic AND/OR bug

The task: "Electronics (category 2) or Accessories (category 4) products under 2000 taka." Written the way you say it:

SELECT name, category_id, price
FROM products
WHERE category_id = 2 OR category_id = 4 AND price < 2000
ORDER BY product_id;

+-----------------------------+-------------+-------+
| name                        | category_id | price |
+-----------------------------+-------------+-------+
| Mechanical Keyboard         |           2 |  4500 |
| Wireless Mouse              |           2 |   900 |
| 27-inch Monitor             |           2 | 18500 |
| Laptop Stand                |           4 |  1500 |
| Noise-Cancelling Headphones |           2 |  7500 |
+-----------------------------+-------------+-------+

A 18,500 taka monitor is not "under 2000". There is no error, just a wrong answer. The reason is precedence: just as × binds tighter than + in arithmetic, NOT binds tighter than AND, and AND tighter than OR. The database read the condition like this:

category_id = 2   OR   (category_id = 4 AND price < 2000)
───────────────        ──────────────────────────────────
all Electronics              only cheap Accessories

Parentheses say what you meant:

SELECT name, category_id, price
FROM products
WHERE (category_id = 2 OR category_id = 4) AND price < 2000
ORDER BY product_id;

+----------------+-------------+-------+
| name           | category_id | price |
+----------------+-------------+-------+
| Wireless Mouse |           2 |   900 |
| Laptop Stand   |           4 |  1500 |
+----------------+-------------+-------+

Rule of thumb: whenever a WHERE mixes AND and OR, add parentheses, even when the default would be right. The next person to read the query (or you, in a month) should not have to remember precedence rules.

Combining conditions

A realistic filter combines several of these. "Card or cash payments of at least 4000 taka":

SELECT payment_id, method, amount, paid_on
FROM payments
WHERE (method = 'card' OR method = 'cash')
  AND amount >= 4000
ORDER BY payment_id;

+------------+--------+--------+------------+
| payment_id | method | amount | paid_on    |
+------------+--------+--------+------------+
|          2 | card   |   4500 | 2026-01-20 |
|          4 | cash   |   4000 | 2026-02-16 |
|          6 | card   |  10000 | 2026-03-10 |
|          7 | card   |   4000 | 2026-03-15 |
|          9 | card   |   4950 | 2026-04-02 |
|         10 | card   |  18500 | 2026-04-19 |
+------------+--------+--------+------------+

Putting each condition on its own line, with AND at the start, makes long filters easy to read and easy to switch off while debugging (comment out one line with --).

A first look at NULL

One more check. Who does not live in Dhaka?

SELECT name, city
FROM customers
WHERE city <> 'Dhaka'
ORDER BY customer_id;

+---------------+------------+
| name          | city       |
+---------------+------------+
| Tanvir Ahmed  | Chattogram |
| Rafiq Islam   | Sylhet     |
| Imran Hossain | Khulna     |
| Karim Uddin   | Rajshahi   |
+---------------+------------+

Sadia Chowdhury (customer 5) is missing, even though nothing says she lives in Dhaka. Her city is NULL (unknown), and a comparison with an unknown value is neither true nor false but unknown, so WHERE drops the row. This is one of the most common silent bugs in SQL; the next page explains it properly, with IS NULL.

Filtering from Python

In application code the filter values usually come from somewhere else: a user, a config file, a loop. Pass them as parameters (? in sqlite3) instead of pasting them into the SQL string:

from sqlhelp import show

max_price = 2000
show("SELECT name, price FROM products WHERE price < ? ORDER BY price", (max_price,))

status = "pending"
show("SELECT order_id, customer_id FROM orders WHERE status = ? ORDER BY order_id", (status,))

+---------------------+-------+
| name                | price |
+---------------------+-------+
| Wireless Mouse      |   900 |
| Gift Card           |  1000 |
| Python Crash Course |  1200 |
| Laptop Stand        |  1500 |
+---------------------+-------+
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
|       13 |           6 |
+----------+-------------+

The database receives the value separately from the SQL, so a value like x' OR '1'='1 is just an odd status that matches nothing, not a piece of code. Building SQL with f-strings is how SQL injection happens; the advanced tutorial shows the attack and the fix in detail.

Where you will use this

• Building a training set: WHERE label IS NOT NULL AND language = 'bn' AND created_at >= '2026-01-01'. Every condition is a decision about what the model learns from, so write it down in the query, not in someone's head.
• Debugging an AI app: WHERE status_code <> 200 AND model = 'gpt-x' on a log table finds the failing calls in seconds.
• RAG metadata filters: before a vector search, WHERE tenant_id = ? AND doc_type = 'policy' limits which chunks may be returned at all. Getting this filter wrong leaks one customer's documents to another.

Common mistakes

1. Mixing AND and OR without parentheses. a OR b AND c means a OR (b AND c). Write (a OR b) AND c when that is what you mean.
2. Using a SELECT alias in WHERE. SQLite allows it as an extension, so the mistake hides until the query moves:
   SQLCopy

   -- PostgreSQL
   SELECT order_id, quantity * unit_price AS line_total
   FROM order_items
   WHERE line_total > 10000;

   OutputCopy

   Error: column "line_total" does not exist

   MySQL says Unknown column 'line_total' in 'where clause'. Repeat the expression instead: WHERE quantity * unit_price > 10000.
3. Quoting numbers or comparing numbers stored as text. '9' < '10' is false. Keep numbers in number columns and compare them without quotes.
4. Wrong case or extra spaces in text. WHERE status = 'Delivered' returns nothing in SQLite and PostgreSQL. Check the real values first with SELECT DISTINCT status FROM orders.
5. <> forgetting NULLs. city <> 'Dhaka' also drops rows where city is NULL. If they should count, say so: WHERE city <> 'Dhaka' OR city IS NULL.

Try it yourself

1. Easy: List the products that cost at least 5000 taka, most expensive first.
2. Medium: List the delivered orders placed on or after 1 March 2026 by customer 1 or customer 3, oldest first.
3. Medium: List the employees in the Data department who earn more than 100000, or anyone hired before 2023.
4. Hard: Find payments that are either card payments of at least 5000 taka or bKash payments under 3000 taka. Then write a query for "orders that are neither delivered nor cancelled" twice: once with NOT ( … OR … ) and once without NOT, and check that both return the same rows.

Answers

-- 1
SELECT name, price
FROM products
WHERE price >= 5000
ORDER BY price DESC;

-- 2: the parentheses keep "customer 1 or 3" together
SELECT order_id, customer_id, order_date
FROM orders
WHERE (customer_id = 1 OR customer_id = 3)
  AND status = 'delivered'
  AND order_date >= '2026-03-01'
ORDER BY order_date, order_id;

-- 3: AND binds first, but the parentheses make it obvious
SELECT name, department, salary, hired_on
FROM employees
WHERE (department = 'Data' AND salary > 100000)
   OR hired_on < '2023-01-01'
ORDER BY employee_id;

-- 4a
SELECT payment_id, method, amount
FROM payments
WHERE (method = 'card' AND amount >= 5000)
   OR (method = 'bkash' AND amount < 3000)
ORDER BY payment_id;

-- 4b: NOT (A OR B) is the same as NOT A AND NOT B
SELECT order_id, status
FROM orders
WHERE NOT (status = 'delivered' OR status = 'cancelled')
ORDER BY order_id;

SELECT order_id, status
FROM orders
WHERE status <> 'delivered' AND status <> 'cancelled'
ORDER BY order_id;

Both 4b queries return orders 12 (shipped) and 13 (pending). The rule they demonstrate, NOT (A OR B) = NOT A AND NOT B, is called De Morgan's law, and it is handy when you need to turn a filter inside out.

Summary

• WHERE keeps rows where the condition is true. It runs right after FROM, before SELECT, so it cannot use SELECT aliases (SQLite's leniency aside).
• Comparisons: = <> < <= > >=. Numbers without quotes, text and dates in single quotes; ISO dates compare correctly as text.
• Text comparison is case-sensitive in SQLite and PostgreSQL, case-insensitive under MySQL's default collation.
• NOT before AND before OR. When AND and OR mix, use parentheses.
• Pass filter values from Python as parameters, never by pasting them into the SQL text.

Next: BETWEEN, IN, LIKE and NULL. You will write ranges and lists more compactly, search text with patterns, and finally deal properly with NULL, the reason Sadia vanished from the last query.