MySQL WHERE Clause

Introduction

In our previous blog, we learned the basics of MySQL, including: MySQL introduction, its features, and why it is the most popular open-source relational database, MySQL RDBMS, and MySQL SELECT Statement.

Now, we are going one step further with MySQL WHERE Clause. The WHERE clause is one of the most important parts of any SQL query because it allows you to filter records and fetch only the data that meets specific conditions. Without WHERE, you’d end up retrieving all records, which is inefficient and unnecessary for most real-world applications.

In this blog, we will also explore advanced uses of WHERE with operators like AND, OR, and NOT to create complex filtering conditions.

What is the MySQL WHERE Clause?

The WHERE clause filters rows returned by a SELECT, UPDATE, or DELETE statement. Think of it as a question you ask the database: “Give me rows that meet this condition.” Without WHERE, queries act on every row.

Use cases: find customers from a city, delete old records, update a single user’s status, and more.

WHERE Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

condition can use comparison operators, logical operators (AND, OR, NOT), and special operators like LIKE, IN, BETWEEN, IS NULL.

Demo Database (Customers)

For example, we’ll use a simple demo table called Customers. Here is an excerpt from the Northwind sample database’s “Customers” table:

CustomerID	Customer Name	Address	City	Postal Code	Country
1	Alfreds	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Christina Berglund	Berguvsvägen 8	Lulea	S-958 22	Sweden

Example:

SELECT * FROM Customers
WHERE Country = 'UK';

Text Fields vs. Numeric Fields

Text values must be surrounded by single quotes in SQL (double quotes are permitted in most database systems).

Numerical fields, however, shouldn’t be surrounded by quotes:

SELECT * FROM Customers
WHERE CustomerID = 2;

Special Operators in WHERE

LIKE — pattern match (use with wildcards % and _).
- % matches any number of characters. Example: WHERE CustomerName LIKE 'A%' matches names starting with A.
- _ matches exactly one character.
IN — match a value in a list. Example: WHERE Country IN ('Germany','Spain');
BETWEEN — range (inclusive). Example: WHERE Age BETWEEN 18 AND 30;
IS NULL / IS NOT NULL — test for NULL values.
!= not equal (<> also be used)
= equal
> greater than
< less than
>= greater or equal
<= less or equal

Examples:

-- Names starting with 'Al'
SELECT * FROM Customers WHERE CustomerName LIKE 'Al%';

-- Countries in a list
SELECT * FROM Customers WHERE Country IN ('Germany','Spain');

-- Postal codes in a range
SELECT * FROM Customers WHERE PostalCode BETWEEN '10000' AND '19999';

-- Missing addresses
SELECT * FROM Customers WHERE Address IS NULL;

MySQL AND, OR and NOT Operators

AND Syntax

condition1 AND condition2

The AND operator returns a record if both conditions separated by AND are TRUE.

SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

OR Syntax

condition1 OR condition2

The OR operator returns records if at least one condition separated by OR is TRUE.

SELECT * FROM Customers
WHERE City = 'Berlin' OR City = 'Stuttgart';

NOT Syntax

NOT condition — negates a condition.

SELECT * FROM Customers
WHERE NOT Country = 'Germany';

MySQL AND, OR and NOT Operators

AND Example (Demo)

SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

This selects all fields from Customers where Country is Germany AND City is Berlin.

Example

Number of Records: 1

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany

OR Example

-- Select customers where city is Berlin OR Stuttgart
SELECT * FROM Customers
WHERE City = 'Berlin' OR City = 'Stuttgart';

Number of Records: 2

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
86	Die Wandernde Kuh	Rita Müller	Adenauerallee 900	Stuttgart	70563	Germany

NOT Example

-- Customers where country is NOT Germany
SELECT * FROM Customers
WHERE Country <> 'Germany';

Same as: WHERE Country NOT IN ('Germany');

This excludes all rows from Germany.

Combining AND, OR and NOT (Precedence & Parentheses)

MySQL evaluates NOT first, then AND, then OR. Use parentheses to make intent clear.

Potential Pitfall (without parentheses)

SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin' OR City = 'Stuttgart';

This is interpreted as:

(Country = 'Germany' AND City = 'Berlin') OR (City = 'Stuttgart')

So it returns either: customers from Germany & Berlin OR anyone in Stuttgart — NOT just Germany + (Berlin or Stuttgart).

Use Parentheses to be Explicit

SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');

This returns customers in Germany whose city is Berlin or Stuttgart.

Performance Tips & Best Practices

Index columns used in WHERE — speeds up queries. Example: CREATE INDEX idx_country ON Customers(Country);
Avoid leading wildcards in LIKE ('%abc') — these prevent index use. Prefer 'abc%' when possible.
Don’t wrap columns in functions in the WHERE clause (e.g., LOWER(name) = 'john') — it may stop index use.
Use EXPLAIN to inspect query performance and execution plan.
Use parameterized queries or prepared statements to avoid SQL injection when accepting user input.
Limit results during testing: LIMIT 10;

Common Mistakes

Comparing to NULL with = instead of IS NULL.
Missing quotes for text values.
Wrong operator precedence when combining AND and OR (forgetting parentheses).
Using SELECT * in production instead of selecting only required columns.

Conclusion

The MySQL WHERE Clause is a powerful tool for narrowing down your search results and retrieving exactly the records you want. Whether you’re filtering based on numbers, text, or combining multiple conditions using AND, OR, and NOT, mastering WHERE will make you a more efficient SQL user.

Now that you understand the WHERE clause, you’re ready to move on to the next blog, where we will cover:

MySQL ORDER BY – how to sort data in ascending or descending order based on specific columns.
MySQL INSERT – how to add new records into your database.

Request more information

MySQL WHERE Clause