MySQL WHERE clause

➔ The WHERE clause must come after the FROM clause and before GROUP BY, HAVING, or ORDER BY.

➔ Although the WHERE clause is optional, omitting it causes the query to operate on all rows, as no conditions are applied.

➔ The conditions in the WHERE clause evaluate to TRUE, FALSE, or UNKNOWN (for the NULL value), and only true conditions are evaluated to retrieve rows.

MySQL WHERE clause syntax

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

Commonly used operators in the WHERE clause:

➔ Comparison: =, <>, !=, <, <=, >, >=.

➔ Logical: AND (all conditions must be true), OR (any condition can be true), and NOT (negates the condition).

➔ Range: BETWEEN is used for inclusive ranges or lists of values.

➔ Set: IN is used to match a set of multiple values.

➔ Pattern Matching: To retrieve data using string patterns, the LIKE operator is used with wildcards (% for zero or more characters, _ for one character) and REGEXP for regular expressions.

➔ NULL checking: IS NULL and IS NOT NULL are used to check whether a value is null or not (never use = NULL, as it will not evaluate to true).

WHERE vs. HAVING

➔ Lifecycle: Although both filter data, they work at different stages of the query lifecycle.

➔ Row-level: The WHERE filter filters individual rows before any grouping or aggregation is performed.

➔ Aggregate Constraint: HAVING clause to filter results after grouping them. The WHERE clause cannot contain aggregate functions (such as SUM or COUNT).

Best performance practices for WHERE clauses

➔ Indexed columns should always be included in the WHERE clause to help the MySQL query optimizer find rows faster.

➔ It is faster to compare a value in a column (e.g., hire_date > '2025-01-01') than to apply a function to that column (e.g., YEAR(hire_date) = 2025).

➔ When matching multiple values, the IN operator is often more readable and can be better optimized by the engine.

-- Query using OR operator
SELECT first_name, lower(last_name) 
FROM employees 
WHERE employee_id=100 OR employee_id=101;

-- Query using IN operator
SELECT first_name, lower(last_name) 
FROM employees 
WHERE employee_id  IN (100,101);

String comparison

Example: Select the employee whose first name is 'Neena'.

SELECT first_name, last_name, employee_id, department_id 
FROM employees 
WHERE first_name = 'Neena';

Numeric Comparison (=, >, <, >=)

Example: Selects employee with an ID greater than 200.

SELECT first_name, last_name, employee_id, department_id 
FROM employees 
WHERE employee_id > 200;

Pattern Matching (LIKE)

Example: Finds employees whose first_name starts with "Ne".

SELECT first_name, last_name, employee_id, department_id 
FROM employees 
WHERE first_name LIKE 'Ne%';

Example: Finds employees whose second letter in their first_name is "e".

SELECT first_name, last_name, employee_id, department_id 
FROM employees 
WHERE first_name LIKE '_e%';

Example: Finds employees whose first name contains "en" anywhere.

SELECT first_name, last_name, employee_id, department_id 
FROM employees 
WHERE first_name LIKE '%en%';

Multiple Conditions (AND, OR)

Example: Find the employees in both departments 80 and 90.

SELECT first_name, last_name, employee_id,salary, department_id 
FROM employees 
WHERE department_id = 80 OR department_id = 90;

Example: Find employees in both departments 100 and 110 whose salary is more than 6,000.

SELECT first_name, last_name, employee_id,salary, department_id 
FROM employees 
WHERE (department_id = 100 OR department_id = 110) AND salary > 6000;

BETWEEN operator in WHERE clause

Example: Find employees in category 80 whose salary is between 8,000 and 11,000.

SELECT first_name, last_name, employee_id, salary, job_id, department_id 
FROM employees 
WHERE department_id = 80 AND salary BETWEEN 8000 AND 11000;

IN operator in WHERE clause

Example: Find employees working in 'SA_REP', 'HR_REP' job id.

SELECT first_name, last_name, employee_id, salary, job_id, department_id 
FROM employees 
WHERE job_id IN ('SA_REP', 'HR_REP');

NULL check in WHERE clause

Example: Selects employees who do not have a department id recorded.

SELECT first_name, last_name, employee_id, salary, job_id, hire_date, department_id 
FROM employees 
WHERE department_id IS NULL;