? MySQL `WHERE` Clause

⚡ Quick Overview

The WHERE clause is used to filter records in SQL queries. It specifies the condition that must be met for the rows to be included in the query result. You can use WHERE with SELECT, UPDATE, and DELETE statements to decide which rows are returned or affected.

? Goal: Filter rows

? Key Concepts

WHERE: Adds a condition to filter the result set.
AND: Combines multiple conditions; all must be true.
OR: Combines conditions; at least one must be true.
NOT: Reverses the result of a condition.
BETWEEN: Selects values within a specific range (inclusive).
!= or <>: Checks for inequality.
Used with SELECT, UPDATE and DELETE queries.

? Syntax

? View Code Example

-- Basic WHERE syntax in a SELECT query
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- WHERE with multiple conditions (AND)
SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

-- WHERE with OR condition
SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

-- WHERE with NOT condition
SELECT column1, column2
FROM table_name
WHERE NOT condition;

-- WHERE in UPDATE statement
UPDATE table_name
SET column1 = value1
WHERE condition;

-- WHERE in DELETE statement
DELETE FROM table_name
WHERE condition;

? Practical Examples

Assume we have a table students with columns: name, age, and department.

? View Code Example

-- Select students who are older than 18
SELECT name, age
FROM students
WHERE age > 18;

-- Select students from 'Engineering' department
SELECT name, department
FROM students
WHERE department = 'Engineering';

-- Select students whose age is between 20 and 25
SELECT name, age
FROM students
WHERE age BETWEEN 20 AND 25;

-- Select students who are not from 'Engineering' department
SELECT name, department
FROM students
WHERE department != 'Engineering';

? What These Queries Do

Age > 18: Returns only students whose age value is greater than 18.
department = 'Engineering': Filters rows to show only Engineering students.
BETWEEN 20 AND 25: Includes students whose age is 20, 21, 22, 23, 24, or 25.
department != 'Engineering': Returns all students except those in Engineering.

Always remember: if a row does not satisfy the WHERE condition, it will be excluded from the result set (or left unchanged in case of UPDATE/DELETE).

? Tips & Best Practices

Use WHERE to narrow down queries to only relevant rows and improve performance.
Combine conditions with AND and OR, and use parentheses ( ) to control logic clearly.
Always test your WHERE clause using a SELECT before running UPDATE or DELETE.
Be careful with inequality and range conditions; confirm boundary values (e.g., with BETWEEN both ends are included).
Handle NULL values correctly using IS NULL and IS NOT NULL instead of = NULL.
When in doubt, start with simple conditions and gradually add more using AND/OR.

?‍? Try It Yourself / Practice Tasks

Write a SELECT query on a students table that returns students from the 'Science' department who are older than 19.
Use BETWEEN to select employees whose salary is between 30,000 and 50,000.
Write a query that excludes rows based on certain conditions using NOT (for example, students who are not in 1st year).
Experiment with IN to select rows where a department is one of several values (e.g., 'IT', 'CS', 'ECE').
Use LIKE to filter students whose names start with the letter 'A'.