WHERE Clause

WHERE Clause in MySQL

Introduction

When working with databases, you often need to retrieve specific data instead of all records.

This is done using the WHERE clause in MySQL.

The WHERE clause allows you to filter rows based on conditions, making queries more precise and efficient.

What is the WHERE Clause

The WHERE clause is used to filter records in SQL queries.

It is commonly used with:

• SELECT
• UPDATE
• DELETE

It ensures that only rows satisfying a condition are affected.

Basic Syntax

SELECT column_name

FROM table_name

WHERE condition;

Example

SELECT *

FROM Students

WHERE age > 20;

This returns only students whose age is greater than 20.

Using WHERE with Different Queries

With SELECT

SELECT name FROM Students

WHERE city = 'Delhi';

With UPDATE

UPDATE Students

SET age = 21

WHERE id = 1;

With DELETE

DELETE FROM Students

WHERE id = 2;

Comparison Operators

Used to compare values:

• = → Equal
• != or <> → Not equal
• → Greater than
• < → Less than
• = → Greater than or equal
• <= → Less than or equal

Example:

SELECT * FROM Students

WHERE age >= 18;

Logical Operators

Used to combine conditions:

AND

SELECT * FROM Students

WHERE age > 18 AND city = 'Delhi';

OR

SELECT * FROM Students

WHERE city = 'Delhi' OR city = 'Mumbai';

NOT

SELECT * FROM Students

WHERE NOT city = 'Delhi';

Special Operators in WHERE

BETWEEN

SELECT * FROM Students

WHERE age BETWEEN 18 AND 25;

IN

SELECT * FROM Students

WHERE city IN ('Delhi', 'Mumbai');

LIKE

SELECT * FROM Students

WHERE name LIKE 'A%';

IS NULL

SELECT * FROM Students

WHERE age IS NULL;

Using Multiple Conditions

SELECT * FROM Students

WHERE age > 18 AND city = 'Delhi';

Order of Conditions

• AND has higher priority than OR
• Use parentheses for clarity

SELECT * FROM Students

WHERE (age > 18 OR city = 'Delhi')

AND gender = 'F';

Important Notes

• WHERE is executed before GROUP BY
• Works row by row
• Cannot use aggregate functions directly (use HAVING instead)

Example Scenario

SELECT * FROM Students

WHERE age BETWEEN 18 AND 25

AND city IN ('Delhi', 'Mumbai');

Common Mistakes

• Missing quotes for text values
• Using = NULL instead of IS NULL
• Forgetting WHERE in UPDATE/DELETE
• Writing unclear conditions

Key Points to Remember

• WHERE filters rows
• Works with SELECT, UPDATE, and DELETE
• Uses operators for conditions
• Supports multiple conditions
• Essential for precise queries