WHERE Clause

WHERE Clause

What it means

WHERE filters which rows a query returns (or which rows an UPDATE/DELETE affects), based on a boolean condition evaluated per row. It supports comparison operators (=, <>, <, >=), logical combinators (AND, OR, NOT), pattern matching (LIKE), range checks (BETWEEN), and list membership (IN).

Examples

SELECT * FROM Products WHERE Price > 50;

SELECT * FROM Products WHERE Price BETWEEN 50 AND 100;   -- inclusive on both ends

SELECT * FROM Customers WHERE Country = 'USA' AND IsActive = 1;

SELECT * FROM Customers WHERE Country = 'USA' OR Country = 'Canada';
SELECT * FROM Customers WHERE Country IN ('USA', 'Canada', 'Mexico');   -- cleaner equivalent

SELECT * FROM Customers WHERE Name LIKE 'A%';        -- starts with 'A'
SELECT * FROM Customers WHERE Email LIKE '%@gmail.com';   -- ends with '@gmail.com'

SELECT * FROM Orders WHERE NOT (Status = 'Cancelled');

Common mistake

Mixing AND and OR without parentheses to group them clearly — SQL evaluates AND before OR by default (like standard operator precedence), which can silently produce a different condition than what was intended.

-- Intended: "USA customers who are active, OR any Canada customer"
-- Actual (due to AND binding tighter than OR): "USA AND active" OR "just Canada" (any status!)
SELECT * FROM Customers
WHERE Country = 'USA' AND IsActive = 1 OR Country = 'Canada';

-- Fix: use parentheses to make the intended grouping explicit
SELECT * FROM Customers
WHERE (Country = 'USA' AND IsActive = 1) OR Country = 'Canada';

Quick practice

1. What does WHERE Price BETWEEN 50 AND 100 include — is 100 itself matched?

   Answer

   Yes — BETWEEN is inclusive on both ends, so rows with Price exactly 50 or exactly 100 are included.

2. Why should you use parentheses when mixing AND and OR in the same WHERE clause?

   Answer

   Without them, SQL applies standard operator precedence (AND binds tighter than OR), which can group conditions differently than you intended — parentheses make the grouping explicit and unambiguous.

3. What does WHERE Name LIKE 'A%' match?

   Answer

   Any value where Name starts with the letter "A" — % is a wildcard matching any sequence of characters (including none).