MySQL OR Operator

Introduction

In this chapter, we will learn about the OR operator in MySQL. The OR operator is used to combine multiple conditions in a SQL statement, ensuring that at least one of the specified conditions is true for a row to be included in the result set. The OR operator is commonly used in SELECT, UPDATE, DELETE, and other SQL statements to filter data based on multiple criteria. We will cover the syntax, examples, and important considerations for using the OR operator.

Syntax

The basic syntax for the OR operator is:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR ...;

• condition1, condition2, ...: The conditions that must be true for a row to be included in the result set.

Using OR Operator

Example with SELECT

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

This example retrieves 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.

Example with UPDATE

UPDATE students
SET email = 'updated@example.com'
WHERE first_name = 'Rahul' OR last_name = 'Sharma';

This example updates the email column for the rows where the first_name is 'Rahul' or the last_name is 'Sharma'.

Example with DELETE

DELETE FROM students
WHERE first_name = 'Rahul' OR last_name = 'Sharma';

This example deletes the rows where the first_name is 'Rahul' or the last_name is 'Sharma'.

Combining OR with Other Operators

Example with AND

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

This example retrieves 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, and the email ends with '@example.com'.

Example with NOT

SELECT first_name, last_name, email
FROM students
WHERE last_name = 'Sharma' OR NOT email LIKE '%@oldemail.com';

This example retrieves the first_name, last_name, and email columns from the students table where the last_name is 'Sharma' or the email does not end with '@oldemail.com'.

Full Example

Let's go through a full example where we create a table, insert data into it, and use the OR operator 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'),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05');

5. Use the OR Operator with SELECT:

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
Sahil	Mehta	sahil.mehta@example.com

6. Use the OR Operator with UPDATE:

UPDATE students
SET email = 'updated@example.com'
WHERE first_name = 'Rahul' OR last_name = 'Sharma';

7. Verify the Update:

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

Output

first_name	last_name	email
Rahul	Sharma	updated@example.com

8. Use the OR Operator with DELETE:

DELETE FROM students
WHERE first_name = 'Rahul' OR last_name = 'Sharma';

9. Verify the Deletion:

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

Output

(empty result set)

Important Considerations

• Order of Conditions: Ensure that conditions combined with the OR operator are logically ordered and do not conflict with each other.
• Performance: Using multiple conditions with the OR operator can impact performance, especially on large datasets. Ensure appropriate indexing to optimize query performance.

Conclusion

The OR operator is used for combining multiple conditions in MySQL queries. This chapter covered how to use the OR operator with SELECT, UPDATE, and DELETE statements, provided examples, and discussed important considerations. In the next chapter, we will learn how to use the NOT operator to filter data based on negated conditions.