Filtering with WHERE

WHERE decides which rows come back. The database tests your condition against every candidate row and keeps the ones where the condition is true.

That word "true" is doing more work than it appears to. A SQL condition can come out true, false, or unknown, and unknown behaves like false — the row is dropped. Most filtering bugs come from a condition that was unknown when you expected it to be true.

-- Exact match
SELECT company_name FROM customers WHERE country = 'India';

-- Comparison
SELECT product_name FROM products WHERE unit_price > 50;

-- Inclusive range: 10 and 50 are both included
SELECT product_name FROM products WHERE unit_price BETWEEN 10 AND 50;

-- One of several values
SELECT company_name FROM customers WHERE country IN ('India', 'Ireland', 'Italy');

-- Pattern match: % is any number of characters, _ is exactly one
SELECT product_name FROM products WHERE product_name LIKE 'Cable%';

-- Combining, with brackets to make the grouping explicit
SELECT order_id
FROM orders
WHERE order_date >= '2026-01-01'
  AND (status = 'pending' OR status = 'processing');

• Text values go in single quotes. Numbers and dates written as text also use single quotes in SQL Server.
• BETWEEN includes both endpoints. For dates that is a trap worth knowing: BETWEEN '2026-01-01' AND '2026-01-31' on a column that stores a time will miss anything after midnight on the 31st. Prefer >= start AND < next_start for date ranges.
• AND binds tighter than OR, so without the brackets on the last query you would get orders that are pending since January, plus every processing order ever placed. The brackets are not decoration.

NULL means no value is recorded. So when the database compares a NULL to anything, it cannot honestly answer yes or no. It answers unknown.

That includes comparing NULL to NULL. Two unknown values are not known to be equal, so the comparison is unknown rather than true. This is why the following query returns nothing at all, on a table where plenty of customers have no email:

-- Returns zero rows, always, no matter what the data contains
SELECT company_name FROM customers WHERE email = NULL;

-- Correct: IS NULL asks whether the value is absent
SELECT company_name FROM customers WHERE email IS NULL;

-- And the opposite
SELECT company_name FROM customers WHERE email IS NOT NULL;

• email = NULL evaluates to unknown for every row, including rows where email really is NULL. Unknown is not true, so no row is kept.
• IS NULL is a different kind of test. It is not a comparison — it asks a direct question about presence, and it returns true or false, never unknown.
• Note that the first query does not error. It runs, succeeds, and returns an empty result, which is exactly why this mistake survives code review.

This one is worth working through slowly, because it produces an empty result from a query that reads perfectly.

You want customers who have never ordered. orders.customer_id is NOT NULL, so use employees instead: which employees have never been assigned an order? orders.employee_id is nullable, and some orders came in through the website with no employee attached.

-- Returns nothing as soon as ONE order has employee_id = NULL
SELECT e.first_name, e.last_name
FROM employees AS e
WHERE e.employee_id NOT IN (SELECT o.employee_id FROM orders AS o);

-- Fix 1: exclude the nulls from the list
SELECT e.first_name, e.last_name
FROM employees AS e
WHERE e.employee_id NOT IN (
    SELECT o.employee_id FROM orders AS o WHERE o.employee_id IS NOT NULL
);

-- Fix 2: NOT EXISTS, which is immune to this problem
SELECT e.first_name, e.last_name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.employee_id = e.employee_id
);

• NOT IN (a, b, NULL) means employee_id <> a AND employee_id <> b AND employee_id <> NULL.
• That last comparison is unknown for every employee. unknown AND true gives unknown, so the best any row can achieve is unknown — which is not true, so no row is kept.
• Fix 1 works and requires you to remember the problem exists. Fix 2 is the habit worth building: NOT EXISTS asks "is there a matching row?", which is a true-or-false question with no unknown to trip over.