MySQL WHERE

MySQL WHERE Clause

The WHERE clause in MySQL is used to filter records in a query. It specifies a condition that must be true for rows to be included in the result set. The WHERE clause can be used with various SQL statements, including SELECT, UPDATE, DELETE, and more.

Syntax

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

Operators in WHERE Clause

Operator	Description
`=`	Equal to a value
`<>` or `!=`	Not equal to a value
`>`	Greater than a value
`<`	Less than a value
`>=`	Greater than or equal to a value
`<=`	Less than or equal to a value
`BETWEEN`	Between two values (inclusive)
`IN`	Matches any value in a list
`LIKE`	Matches a pattern
`IS NULL`	Checks for `NULL` values
`IS NOT NULL`	Checks for non-`NULL` values

Examples

1. Filtering Rows with Simple Conditions

Get employees with a salary greater than 5000:

SELECT name, salary
FROM employees
WHERE salary > 5000;

2. Using Multiple Conditions with AND/OR

Get employees in the "Sales" department with a salary greater than 4000:

SELECT name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 4000;

Get employees in either "Sales" or "HR":

SELECT name, department
FROM employees
WHERE department = 'Sales' OR department = 'HR';

3. Using BETWEEN

Get orders placed between January 1, 2025, and March 31, 2025:

SELECT order_id, order_date, total
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';

4. Using IN

Get employees with specific IDs:

SELECT name, id
FROM employees
WHERE id IN (101, 102, 103);

5. Using LIKE

Get customers whose names start with "A":

SELECT name
FROM customers
WHERE name LIKE 'A%';

Get customers whose names end with "son":

SELECT name
FROM customers
WHERE name LIKE '%son';

6. Checking for NULL Values

Find orders without a delivery date:

SELECT order_id, customer_id
FROM orders
WHERE delivery_date IS NULL;

7. Combining NOT with Conditions

Get employees who are not in the "Sales" department:

SELECT name, department
FROM employees
WHERE department != 'Sales';

Find employees who are not earning between 3000 and 5000:

SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 3000 AND 5000;

WHERE Clause with Other SQL Statements

1. Using WHERE with UPDATE

Update the salary of employees in the "HR" department:

UPDATE employees
SET salary = salary + 500
WHERE department = 'HR';

2. Using WHERE with DELETE

Delete orders with a total of less than 100:

DELETE FROM orders
WHERE total < 100;

3. Using WHERE with JOIN

Get orders and customer names where the order total is above 500:

SELECT orders.order_id, customers.name, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.total > 500;

Tips and Best Practices

Use Indexed Columns: Using the a WHERE clause on indexed columns improves query performance.
Avoid Functions on Columns: Writing conditions like WHERE YEAR(order_date) = 2025 can bypass indexes.
Use Parameterized Queries: Prevent SQL injection by using parameterized queries in applications.
Test Conditions with SELECT: Test complex conditions with a SELECT query before using them in UPDATE or DELETE.

Let me know if you need further clarification or examples for the WHERE clause!

Souy Soeng