MySQL WHERE Clause

The WHERE clause filters the rows returned by a SELECT, UPDATE, or DELETE statement. Without a WHERE clause, operations apply to every row in the table. With it, only rows that meet a specific condition are affected.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Sample Table: students

+------------+------------+-----------+-----+-------------------+
| student_id | first_name | last_name | age | email             |
+------------+------------+-----------+-----+-------------------+
|          1 | Alice      | Johnson   |  20 | alice@example.com |
|          2 | Bob        | Smith     |  22 | bob@example.com   |
|          3 | Carol      | White     |  19 | carol@example.com |
|          4 | David      | Brown     |  21 | david@example.com |
|          5 | Eve        | Taylor    |  22 | eve@example.com   |
+------------+------------+-----------+-----+-------------------+

Comparison Operators

The WHERE clause uses comparison operators to evaluate conditions:

Example: Filtering by a Specific Value

SELECT first_name, age
FROM students
WHERE age = 22;

Output:

+------------+-----+
| first_name | age |
+------------+-----+
| Bob        |  22 |
| Eve        |  22 |
+------------+-----+

Example: Filtering by a Range

SELECT first_name, age
FROM students
WHERE age >= 21;

Returns students aged 21 or older.

Filtering Text Values

Text values in a WHERE clause must be enclosed in single quotes:

SELECT * FROM students
WHERE last_name = 'Smith';

The BETWEEN Operator

BETWEEN filters rows where a value falls within a range (inclusive of both endpoints):

SELECT first_name, age
FROM students
WHERE age BETWEEN 19 AND 21;

Output: Returns Alice (20), Carol (19), and David (21).

The IN Operator

IN checks if a value matches any value in a list:

SELECT first_name, age
FROM students
WHERE age IN (19, 21, 22);

This is equivalent to writing: WHERE age = 19 OR age = 21 OR age = 22, but is much shorter.

The LIKE Operator

LIKE is used for pattern matching in text. Two wildcards are available:

• % — Matches any sequence of characters (including none).
• _ — Matches exactly one character.

-- Find students whose first name starts with 'A'
SELECT * FROM students
WHERE first_name LIKE 'A%';

-- Find students whose last name ends with 'n'
SELECT * FROM students
WHERE last_name LIKE '%n';

-- Find students whose first name is 5 characters long
SELECT * FROM students
WHERE first_name LIKE '_____';

The NOT Operator with WHERE

NOT reverses a condition:

-- Get all students NOT aged 22
SELECT * FROM students
WHERE NOT age = 22;

-- Get students whose last name is NOT 'Smith'
SELECT * FROM students
WHERE last_name NOT IN ('Smith', 'Brown');

Using WHERE with UPDATE and DELETE

WHERE is equally important in UPDATE and DELETE statements to avoid changing or deleting all rows by mistake:

-- Update only Bob's email
UPDATE students
SET email = 'bob.new@example.com'
WHERE student_id = 2;

-- Delete only Carol's record
DELETE FROM students
WHERE student_id = 3;

Key Points

• The WHERE clause filters rows based on a condition.
• Without WHERE, a SELECT returns all rows; an UPDATE or DELETE affects all rows.
• Use BETWEEN for range filtering and IN for matching a list of values.
• Use LIKE with % and _ wildcards for pattern matching in text.
• Text values in conditions must be enclosed in single quotes.