WHERE Conditions

The WHERE clause is used to filter query results, returning only records that meet the conditions. This is a very important function in SQL that allows you to precisely control query results.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Comparison Operators

Basic Comparison

-- Equal to
SELECT * FROM users WHERE age = 25;

-- Not equal to
SELECT * FROM users WHERE age != 25;

-- Greater than
SELECT * FROM users WHERE age > 25;

-- Less than
SELECT * FROM users WHERE age < 25;

-- Greater than or equal to
SELECT * FROM users WHERE age >= 25;

-- Less than or equal to
SELECT * FROM users WHERE age <= 25;

Example: Query Users of Specific Age

-- Query users aged 25 or older
SELECT name, age, city
FROM users
WHERE age >= 25;

Result:

name	age	city
Zhang San	25	Beijing
Li Si	30	Shanghai
Wang Wu	28	Guangzhou
Zhao Liu	35	Beijing

Logical Operators

AND Operator

Satisfy multiple conditions simultaneously:

-- Query users aged 25 to 30 from Beijing
SELECT name, age, city
FROM users
WHERE age >= 25
  AND age <= 30
  AND city = 'Beijing';

OR Operator

Satisfy any condition:

-- Query users from Beijing or Shanghai
SELECT name, city
FROM users
WHERE city = 'Beijing' OR city = 'Shanghai';

NOT Operator

Negate condition:

-- Query users not from Beijing
SELECT name, city
FROM users
WHERE NOT city = 'Beijing';

-- Equivalent to
SELECT name, city
FROM users
WHERE city != 'Beijing';

Operator Precedence

-- Use parentheses to clarify precedence
SELECT name, age, city
FROM users
WHERE (age > 25 AND city = 'Beijing')
   OR (age < 25 AND city = 'Shanghai');

Range Query

BETWEEN Operator

Query values within a specified range (including boundary values):

-- Query users aged 25 to 30
SELECT name, age
FROM users
WHERE age BETWEEN 25 AND 30;

Equivalent to:

SELECT name, age
FROM users
WHERE age >= 25 AND age <= 30;

Date Range Query

-- Query orders within a specific date range
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Set Query

IN Operator

Query records where values are in a specified set:

-- Query users from Beijing, Shanghai, Guangzhou
SELECT name, city
FROM users
WHERE city IN ('Beijing', 'Shanghai', 'Guangzhou');

Equivalent to:

SELECT name, city
FROM users
WHERE city = 'Beijing'
   OR city = 'Shanghai'
   OR city = 'Guangzhou';

NOT IN Operator

-- Query users not from Beijing, Shanghai, Guangzhou
SELECT name, city
FROM users
WHERE city NOT IN ('Beijing', 'Shanghai', 'Guangzhou');

IN in Subqueries

-- Query users who have orders
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

Fuzzy Query

LIKE Operator

Use wildcards for fuzzy matching:

Percent Sign %

% matches any number of characters (including zero):

-- Query users with surname "Zhang"
SELECT name FROM users WHERE name LIKE 'Zhang%';

-- Query users whose email ends with example.com
SELECT name, email FROM users
WHERE email LIKE '%@example.com';

-- Query users whose email contains 'example'
SELECT name, email FROM users
WHERE email LIKE '%example%';

Underscore _

_ matches a single character:

-- Query users whose second character is 'San'
SELECT name FROM users WHERE name LIKE '_San%';

-- Query users whose email prefix is 5 characters
SELECT email FROM users WHERE email LIKE '_____@%';

Example: Flexible Fuzzy Query

-- 1. Query users whose name contains "Zhang"
SELECT * FROM users WHERE name LIKE '%Zhang%';

-- 2. Query users whose email ends with 'gmail.com'
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 3. Query users whose name length is 2
SELECT * FROM users WHERE name LIKE '__';

ESCAPE Character

Handle strings containing special characters:

-- Query strings containing percent sign
SELECT * FROM products
WHERE description LIKE '50\% discount' ESCAPE '\';

NULL Value Handling

IS NULL and IS NOT NULL

-- Query users who haven't filled email
SELECT name, email FROM users WHERE email IS NULL;

-- Query users who have filled email
SELECT name, email FROM users WHERE email IS NOT NULL;

NULL Characteristics

-- NULL cannot use = or != for comparison
-- Wrong way
SELECT * FROM users WHERE email = NULL;

-- Correct way
SELECT * FROM users WHERE email IS NULL;

COALESCE and NULL Handling

-- Provide default value for NULL values
SELECT
    name,
    COALESCE(phone, 'Phone not filled') AS phone
FROM users;

Combined Conditions

Complex Condition Combination

-- Query users over 25 from Beijing or Shanghai
SELECT name, age, city
FROM users
WHERE age > 25
  AND (city = 'Beijing' OR city = 'Shanghai');

Use Parentheses for Grouping

-- Note the use of parentheses
SELECT name, age, city
FROM users
WHERE (age > 25 AND city = 'Beijing')
   OR (age < 25 AND city = 'Shanghai');

Common Condition Patterns

Pattern 1: Date Filtering

-- Query orders from the last 7 days
SELECT * FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Query orders from this month
SELECT * FROM orders
WHERE MONTH(order_date) = MONTH(NOW())
  AND YEAR(order_date) = YEAR(NOW());

Pattern 2: Numeric Range

-- Query products priced between 100 and 500
SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

Pattern 3: Multi-condition Combination

-- Query active users who logged in recently
SELECT * FROM users
WHERE status = 'active'
  AND last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND email IS NOT NULL;

WHERE vs HAVING

WHERE

• Filter before grouping
• Cannot use aggregate functions

HAVING

• Filter after grouping
• Can use aggregate functions

Example Comparison

-- Using WHERE (correct)
SELECT city, COUNT(*)
FROM users
WHERE age > 18
GROUP BY city;

-- Using HAVING
SELECT city, COUNT(*)
FROM users
GROUP BY city
HAVING COUNT(*) > 5;

Performance Optimization

1. Use Indexes

-- Create indexes for common query conditions
CREATE INDEX idx_user_age ON users(age);
CREATE INDEX idx_user_city ON users(city);

2. Avoid Using Functions in WHERE

-- Not recommended: Using functions on columns
SELECT * FROM users
WHERE YEAR(created_at) = 2024;

-- Recommended: Use range query
SELECT * FROM users
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

3. Use EXISTS Instead of IN

-- Not recommended: IN subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Recommended: EXISTS subquery
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Practical Examples

Example 1: User Filtering

-- Query active users aged 25-35 from Beijing or Shanghai
SELECT
    name,
    age,
    city
FROM users
WHERE age BETWEEN 25 AND 35
  AND city IN ('Beijing', 'Shanghai')
  AND status = 'active'
ORDER BY age;

Example 2: Order Query

-- Query orders with amount greater than 500 this month
SELECT
    order_id,
    user_id,
    total_amount,
    order_date
FROM orders
WHERE order_date >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND total_amount > 500
ORDER BY total_amount DESC;

Example 3: Product Search

-- Search for products where name or description contains keywords
SELECT
    name,
    price,
    stock
FROM products
WHERE name LIKE '%keyword%'
   OR description LIKE '%keyword%'
ORDER BY price;

Common Errors

Error 1: Confusing = and ==

-- Error
SELECT * FROM users WHERE age == 25;

-- Correct
SELECT * FROM users WHERE age = 25;

Error 2: Strings Without Quotes

-- Error
SELECT * FROM users WHERE city = Beijing;

-- Correct
SELECT * FROM users WHERE city = 'Beijing';

Error 3: NULL Using Comparison Operators

-- Error
SELECT * FROM users WHERE email = NULL;

-- Correct
SELECT * FROM users WHERE email IS NULL;

Summary

This chapter introduced various uses of the WHERE clause:

• Comparison Operators: =, !=, >, <, >=, <=
• Logical Operators: AND, OR, NOT
• Range Query: BETWEEN
• Set Query: IN, NOT IN
• Fuzzy Query: LIKE, %, _
• NULL Handling: IS NULL, IS NOT NULL
• Combined Conditions: Use parentheses for grouping
• WHERE vs HAVING: Different filtering timing
• Performance Optimization: Use indexes, avoid functions, use EXISTS

The WHERE clause is the core of SQL queries. Mastering various condition filtering methods allows you to precisely control query results.

Next Step: Learn Sorting and Pagination to learn how to sort and paginate query results.