MySQL WHERE Clause

Introduction

In this chapter, we will learn about the WHERE clause in MySQL. The WHERE clause is used to filter records that meet specific conditions. It is commonly used in SELECT, UPDATE, DELETE, and other SQL statements to specify which rows should be affected by the query. We will cover the syntax, examples, and important considerations for using the WHERE clause.

Syntax

The basic syntax for the WHERE clause is:

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

The condition specifies the criteria that must be met for a row to be included in the result set. The WHERE clause can also be used with other SQL statements like UPDATE, DELETE, etc.

Operators in WHERE Clause

Comparison Operators

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

Logical Operators

• AND: All conditions must be true
• OR: At least one condition must be true
• NOT: Inverts the condition

Example

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma';

This example selects the first_name, last_name, and email columns from the students table where the last_name is 'Sharma'.

Using AND Operator

The AND operator is used to filter records based on multiple conditions.

Example

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' AND enrollment_date > '2023-01-01';

This example selects the first_name, last_name, and email columns from the students table where the last_name is 'Sharma' and the enrollment_date is after January 1, 2023.

Using OR Operator

The OR operator is used to filter records based on at least one of the specified conditions.

Example

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' OR enrollment_date > '2023-01-01';

This example selects the first_name, last_name, and email columns from the students table where the last_name is 'Sharma' or the enrollment_date is after January 1, 2023.

Using NOT Operator

The NOT operator is used to filter records that do not meet the specified condition.

Example

SELECT first_name, last_name, email
FROM students
WHERE NOT last_name = 'Sharma';

This example selects the first_name, last_name, and email columns from the students table where the last_name is not 'Sharma'.

Combining AND, OR, and NOT Operators

You can combine AND, OR, and NOT operators to create complex conditions.

Example

SELECT first_name, last_name, email
FROM students
WHERE (last_name = 'Sharma' AND enrollment_date > '2023-01-01') OR NOT email LIKE '%example.com';

This example selects the first_name, last_name, and email columns from the students table where the last_name is 'Sharma' and the enrollment_date is after January 1, 2023, or the email does not end with 'example.com'.

Full Example

Let's go through a full example where we create a table, insert data into it, and use the WHERE clause to filter records.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create a Table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

4. Insert Data into the Table:

INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04');

5. Use the WHERE Clause:

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma';

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com

6. Use the AND Operator:

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' AND enrollment_date > '2023-01-01';

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com

7. Use the OR Operator:

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' OR enrollment_date > '2023-01-01';

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com
Priya	Singh	priya.singh@example.com
Amit	Kumar	amit.kumar@example.com
Neha	Verma	neha.verma@example.com

8. Use the NOT Operator:

SELECT first_name, last_name, email
FROM students
WHERE NOT last_name = 'Sharma';

Output

first_name	last_name	email
Priya	Singh	priya.singh@example.com
Amit	Kumar	amit.kumar@example.com
Neha	Verma	neha.verma@example.com

Important Considerations

• Data Types: Ensure that the data types in your query match the data types of the columns.
• Indexing: Use indexing on columns that are frequently used in the WHERE clause to improve query performance.
• Security: Use parameterized queries to prevent SQL injection attacks.

Conclusion

The WHERE clause is used for filtering data in MySQL queries. This chapter covered how to use the WHERE clause with various operators to retrieve specific records from a table.