MySQL - Where Clause

MySQL - Filtering Data with the WHERE Clause

The Purpose of the WHERE Clause

The WHERE clause is one of the most fundamental components of SQL. It is used to filter records returned by a SELECT, UPDATE, or DELETE statement. Without a WHERE clause, these statements would apply to all rows in the table. By specifying conditions in the WHERE clause, you can precisely target the data you want to retrieve or modify.

Syntax

SELECT column_list
FROM table_name
WHERE condition(s);

Syntax and Operators

The WHERE clause uses a variety of operators to build conditions:

Operator	Description	Example
=	Equal to	age = 30
<> or !=	Not equal to	country <> 'USA'
>	Greater than	price > 99.99
<	Less than	stock < 10
>=	Greater than or equal to	score >= 90
<=	Less than or equal to	discount <= 0.5
BETWEEN	Checks if a value is within a range (inclusive)	age BETWEEN 18 AND 65
IN	Checks if a value is in a list of values	status IN ('pending', 'shipped')
LIKE	Simple pattern matching	name LIKE 'J%'
IS NULL	Checks for a NULL value	shipping_date IS NULL
IS NOT NULL	Checks for a non-NULL value	email IS NOT NULL

Example Setup

Let’s use a customers table for our examples.

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    city VARCHAR(50),
    signup_date DATE NOT NULL,
    tier VARCHAR(20) DEFAULT 'Standard'
);

INSERT INTO customers (name, age, city, signup_date, tier) VALUES
('Alice', 32, 'New York', '2022-01-15', 'Gold'),
('Bob', 25, 'Los Angeles', '2022-03-22', 'Silver'),
('Charlie', 45, 'New York', '2021-11-30', 'Gold'),
('Diana', 28, 'Chicago', '2023-01-10', 'Standard'),
('Edward', NULL, 'Chicago', '2023-02-20', 'Standard');

Filtering with Basic and Advanced Operators

Example 1: Using Comparison Operators

Fetch customers older than 30.

SELECT name, age, city FROM customers WHERE age > 30;

Example 2: Using BETWEEN

Fetch customers who signed up in the first quarter of 2022.

SELECT name, signup_date FROM customers
WHERE signup_date BETWEEN '2022-01-01' AND '2022-03-31';

Example 3: Using IN

Fetch all ‘Gold’ and ‘Silver’ tier customers.

SELECT name, tier FROM customers WHERE tier IN ('Gold', 'Silver');

Example 4: Using IS NULL

Find customers for whom age is not recorded.

SELECT name, city FROM customers WHERE age IS NULL;

Combining Conditions

You can create complex filters by combining conditions with AND (all conditions must be true) and OR (at least one condition must be true). AND has higher precedence than OR, so use parentheses () to enforce a specific order of evaluation.

Example 5: Using AND and OR

Fetch all ‘Gold’ tier customers from ‘New York’, OR any customer from ‘Chicago’.

SELECT name, city, tier FROM customers
WHERE (tier = 'Gold' AND city = 'New York') OR (city = 'Chicago');

Security: Preventing SQL Injection

This is the most important concept in this chapter. Never construct SQL queries by concatenating user input directly into the query string. This creates a severe vulnerability called SQL Injection. Always use parameterized queries (also known as prepared statements).

A client application (e.g., Node.js, Python, Java, PHP) would handle this by sending the query template and the user-provided values separately to the database driver, which then safely combines them.

**Vulnerable (DO NOT DO THIS):**
`const query = "SELECT * FROM users WHERE username = '" + userInput + "';";`

**Safe (Using Parameterization):**
`const query = "SELECT * FROM users WHERE username = ?;";`
`db.execute(query, [userInput]);`

Performance: Writing SARGable WHERE Clauses

For fast query performance on large tables, your WHERE clause conditions should be SARGable (Search ARGument Able). This means the condition can be evaluated using an index. The most common mistake that makes a query non-SARGable is applying a function to the indexed column.

**Non-SARGable (Slow):** Cannot use an index on `signup_date`.
`WHERE YEAR(signup_date) = 2022;`

**SARGable (Fast):** Can use an index on `signup_date`.
`WHERE signup_date >= '2022-01-01' AND signup_date < '2023-01-01';`

By transforming the condition to compare the raw column value against a computed value, you allow the database engine to perform an efficient index seek instead of a slow table scan.