• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL WHERE

← Previous Next →

In MySQL, the WHERE clause is used to filter records when retrieving, updating, or deleting data. Without it, SQL statements affect all rows in a table, which is often not what you want. The WHERE clause allows you to specify conditions that determine which rows are selected or modified.

Think of WHERE as a filter for your database queries. It tells MySQL: “Only consider these records that meet certain criteria.” This is essential for precision and efficiency, especially when working with large datasets.

Basic Syntax

The basic syntax of a WHERE clause is straightforward:

SELECT column1, column2
FROM table_name
WHERE condition;

  • SELECT column1, column2 specifies the columns to retrieve.

  • FROM table_name specifies the table.

  • WHERE condition specifies the filtering rule.

Example:

SELECT * FROM students
WHERE age > 18;

This query fetches only students whose age is greater than 18.

Using Comparison Operators

The WHERE clause supports a variety of comparison operators to filter data:

Operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> or != Not equal to

Example:

SELECT name, age FROM students
WHERE age >= 20;

This fetches students who are 20 years or older.

Logical Operators

You can combine multiple conditions using logical operators:

AND

Both conditions must be true.

SELECT * FROM students
WHERE age > 18 AND city = 'Mumbai';

OR

Either condition can be true.

SELECT * FROM students
WHERE city = 'Delhi' OR city = 'Mumbai';

NOT

Excludes specific conditions.

SELECT * FROM students
WHERE NOT city = 'Delhi';

Using BETWEEN

The BETWEEN operator filters values within a range. It is inclusive of the boundary values.

SELECT * FROM students
WHERE age BETWEEN 18 AND 22;

This returns students whose age is 18, 19, 20, 21, or 22.

Using IN

The IN operator allows you to specify multiple values in a single condition.

SELECT * FROM students
WHERE city IN ('Mumbai', 'Delhi', 'Pune');

This fetches students who belong to any of the three specified cities.

Using LIKE

The LIKE operator is used for pattern matching in text columns.

  • % matches any number of characters.

  • _ matches a single character.

Examples:

SELECT * FROM students
WHERE name LIKE 'A%';

Fetches students whose names start with ‘A’.

SELECT * FROM students
WHERE name LIKE '%a';

Fetches students whose names end with ‘a’.

SELECT * FROM students
WHERE name LIKE '_e%';

Fetches students whose second character is ‘e’.

Using IS NULL and IS NOT NULL

To filter NULL values, you use IS NULL or IS NOT NULL:

SELECT * FROM students
WHERE city IS NULL;

SELECT * FROM students
WHERE city IS NOT NULL;

NULL represents missing or undefined data, and these operators help you handle it efficiently.

Combining Multiple Conditions

You can combine comparison, logical, and pattern matching operators in complex queries:

SELECT * FROM students
WHERE age >= 18
AND city = 'Mumbai'
AND name LIKE 'A%';

This fetches students who are 18 or older, live in Mumbai, and whose names start with ‘A’.

Using Arithmetic Expressions in WHERE

The WHERE clause can also handle arithmetic expressions:

SELECT * FROM students
WHERE age + 2 > 20;

Here, MySQL evaluates age + 2 for each row and filters records accordingly.

Best Practices

  1. Always use WHERE when updating or deleting rows to avoid affecting the entire table.

  2. Combine conditions efficiently with AND and OR.

  3. Use indexes on columns frequently used in WHERE conditions to speed up queries.

  4. Be careful with NULL values; comparisons using = or != won’t match NULL.

  5. Use BETWEEN, IN, and LIKE to simplify complex conditions instead of writing long OR statements.

Real-Life Example

Consider a school database with a students table:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

If you want to retrieve all students aged between 18 and 20 who live in Mumbai or Delhi:

SELECT name, age, city
FROM students
WHERE age BETWEEN 18 AND 20
AND city IN ('Mumbai', 'Delhi');

If you want students whose names start with ‘P’ and who are not from Pune:

SELECT name, city
FROM students
WHERE name LIKE 'P%'
AND city <> 'Pune';

These examples show how WHERE allows precise filtering of data based on multiple criteria.

Summary of the Tutorial

  • The WHERE clause filters records when retrieving, updating, or deleting data.

  • You can use comparison operators like =, >, <, >=, <=, <>.

  • Logical operators (AND, OR, NOT) combine multiple conditions.

  • Special operators like BETWEEN, IN, LIKE, IS NULL provide more flexibility.

  • WHERE can be combined with pattern matching, arithmetic expressions, and multiple conditions for advanced queries.

Mastering the WHERE clause is essential for working with MySQL efficiently. It ensures that you retrieve or modify only the records that meet specific criteria, improving accuracy and performance.

Practice Questions

  1. Retrieve all students from the students table whose age is greater than 18.

  2. Select the name and city of students who live in Mumbai.

  3. Fetch students whose age is between 18 and 22.

  4. Retrieve all students whose city is either Delhi or Pune.

  5. List students whose names start with the letter ‘A’.

  6. Find all students whose names end with the letter ‘a’.

  7. Select students whose city is not Mumbai.

  8. Retrieve all students whose city column is NULL.

  9. Find students whose names have ‘e’ as the second character.

  10. List students who are older than 20 and live in Delhi.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top