Learn SQL  /  Filtering & Sorting  /  WHERE Clause

WHERE Clause

WHERE filters which rows are returned by a query, based on a condition. It is the SQL equivalent of subsetting a data frame in R or pandas.

Basic Filtering

R Code
SELECT *
FROM students
WHERE score >= 80;
Output
id | name  | age | score | class
1  | Rafi  | 23  | 88    | A
2  | Meera | 25  | 92    | A

Combining Conditions: AND / OR / NOT

R Code
SELECT *
FROM students
WHERE score >= 80 AND age < 24;
Output
id | name | age | score | class
1  | Rafi | 23  | 88    | A
⚠ Common Mistake
AND is evaluated before OR unless you use parentheses. WHERE class = 'A' OR class = 'B' AND score > 90 does NOT mean '(A or B) and score > 90' - it means 'A, or (B and score > 90)'. Always add parentheses when mixing AND and OR to make the intended grouping explicit.

BETWEEN, IN, and IS NULL

R Code
SELECT name, score FROM students
WHERE score BETWEEN 70 AND 90;

SELECT name, class FROM students
WHERE class IN ("A", "C");

SELECT name FROM students
WHERE score IS NOT NULL;
Output
name  | score
Rafi  | 88
Zayn  | 76

name  | class
Rafi  | A
Meera | A
💡 Tip
Never write score = NULL to check for a missing value - it always evaluates to unknown, never true. Always use IS NULL or IS NOT NULL instead.

Pattern Matching with LIKE

R Code
SELECT name FROM students
WHERE name LIKE "M%";    -- starts with M

SELECT name FROM students
WHERE name LIKE "%a";    -- ends with a
Output
name
Meera

name
Meera
Nadia

% matches any sequence of characters, and _ matches exactly one character.

Advanced: Combining Multiple Filter Techniques

🚀 Advanced Example
Real filtering conditions often combine several of these techniques together.
R Code
SELECT name, score, class
FROM students
WHERE (class IN ("A", "B"))
  AND score BETWEEN 70 AND 95
  AND name NOT LIKE "Z%";
Output
name  | score | class
Rafi  | 88    | A
Meera | 92    | A

Key Points

  • WHERE filters rows before they are returned, based on a condition.
  • Use parentheses when combining AND with OR to avoid ambiguous evaluation order.
  • Use IS NULL / IS NOT NULL to check for missing values - never = NULL.
  • LIKE with % (any sequence) and _ (single character) enables simple pattern matching.