When querying data in MySQL, we often need to filter out records that meet specific conditions from a large dataset. At this point, the WHERE clause comes in handy—it helps us precisely filter the desired data, like picking out red, spotted apples from a pile. This article will guide you through the usage of the WHERE clause with simple language and examples.

1. What is the WHERE Clause?¶

The WHERE clause is part of the SELECT statement, following the FROM table name, and specifies the conditions for querying data. Only records that satisfy the conditions will be returned; those that don’t are “filtered out.”

For example, suppose we have a students table with columns for name, age, and score. To query students aged 18:

SELECT * FROM students 
WHERE age = 18;

Here, age = 18 is the filter condition, * selects all columns, and FROM students specifies the table.

2. Basic Conditions: Equals (=) and Not Equals (!= or <>)¶

The most common conditions are “equals” and “not equals,” applicable to numbers, strings, dates, etc.

a. Equals (=)¶

Matches exact values:
- Numeric type: age = 18 (no quotes needed)
- String type: name = 'Zhang San' (strings must be enclosed in single quotes ' ')

Example: Query students named “Li Si”:

SELECT * FROM students 
WHERE name = 'Li Si';

b. Not Equals (!= or <>)¶

Matches values that are not equal:
Example: Query students with scores not equal to 60:

SELECT * FROM students 
WHERE score != 60;  -- Use <> instead of != if preferred

3. Range Conditions: Greater Than, Less Than, and BETWEEN¶

To filter data “within a range,” use greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), or the more concise BETWEEN...AND....

a. Greater Than/Less Than¶

• Age > 18: age > 18
• Score < 60: score < 60

Example: Query students aged over 18 and with scores over 80:

SELECT * FROM students 
WHERE age > 18 AND score > 80;

b. BETWEEN…AND… (Range Query)¶

Matches values between A and B (inclusive of A and B), more concise than >= and <=:
Example: Query students aged between 18 and 22 (inclusive):

SELECT * FROM students 
WHERE age BETWEEN 18 AND 22;

Note: The smaller value must come first (e.g., 18 AND 22), otherwise no results will be returned.

4. Logical Operators: AND, OR, NOT¶

To combine multiple conditions, use AND (both conditions must be true), OR (either condition is true), and NOT (negation).

a. AND (Both Conditions)¶

Example: Query male students aged over 18:

SELECT * FROM students 
WHERE age > 18 AND gender = 'Male';

b. OR (Either Condition)¶

Example: Query students aged under 15 or with scores over 90:

SELECT * FROM students 
WHERE age < 15 OR score > 90;

c. NOT (Negation)¶

Example: Query students not in “Senior 3”:

SELECT * FROM students 
WHERE class NOT = 'Senior 3';

5. Fuzzy Matching: LIKE (Partial Character Match)¶

To query records where a name “contains a specific character” or “starts/ends with a character,” use LIKE with wildcards % (any number of characters) or _ (single character).

• %: Matches any length of characters (including 0). For example, 'Li%' matches all names starting with “Li”.
• _: Matches exactly one character. For example, 'Zhang_' matches “Zhang” followed by one character (e.g., “Zhang San”).

Examples:
1. Query students with “Zhang” in their name:

   SELECT * FROM students 
   WHERE name LIKE '%Zhang%';

2. Query students with “Wang” as the first character and a 2-character name (e.g., “Wang Wu”):

   SELECT * FROM students 
   WHERE name LIKE 'Wang_';

6. Special Condition: Handling NULL Values (IS NULL / IS NOT NULL)¶

In databases, “no value” is represented by NULL (not the string 'NULL'). Use IS NULL or IS NOT NULL to check for nulls (cannot use = or !=).

Example: Query students with empty class information:

SELECT * FROM students 
WHERE class IS NULL;

7. Common Errors and Notes¶

1. Strings require single quotes: name = 'Zhang San' (correct), but name = Zhang San will cause an error.
2. BETWEEN includes both endpoints: BETWEEN 18 AND 22 includes 18 and 22; 18 < age < 22 does not.
3. Logical operator precedence: AND has higher precedence than OR. Use parentheses for clarity: e.g., (age > 18 AND score > 80) OR (age < 15 AND score > 90).
4. Avoid NULL with =: class = NULL returns no results; use class IS NULL.

Summary¶

The WHERE clause is a core tool for filtering data in MySQL. Mastering it requires understanding condition types (equality, range, fuzzy, logical) and special handling (null values, string quotes). Practice with simple tables (e.g., student, order) to become proficient!

Now, try creating a simple table in your MySQL environment and use the WHERE clause to filter data under different conditions to reinforce your understanding.