SQL OR Operator

Introduction

In this chapter, we will learn how to use the OR operator in SQL. The OR operator is used to combine multiple conditions in a SQL query. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the OR operator effectively.

What is the OR Operator?

The OR operator is used to combine two or more conditions in a SQL statement. When using the OR operator, at least one of the conditions must be true for the row to be included in the result set. It is commonly used in the WHERE clause to filter records based on multiple criteria.

Syntax for OR

Basic Syntax

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

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table from which you want to retrieve data.
• condition1, condition2, ...: The conditions that must be true for a row to be included in the result set.

Example

Assume we have a table named employees:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 OR salary > 60000;

This command retrieves the first_name, last_name, and email columns from the employees table where the department_id is 1 or the salary is greater than 60000.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

3. Using the OR Operator

To retrieve employees in department 1 or employees with a salary greater than 60000:

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 OR salary > 60000;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

4. Combining Multiple Conditions

You can combine more than two conditions using the OR operator.

Example

To retrieve employees in department 1, employees with a salary greater than 60000, or employees whose first name starts with 'R':

SELECT first_name, last_name, email
FROM employees
WHERE department_id = 1 OR salary > 60000 OR first_name LIKE 'R%';

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

Using OR with Other Operators

The OR operator can be used with other SQL operators like AND, NOT, IN, BETWEEN, etc., to create more complex queries.

Example with AND

To retrieve employees in department 1 with a salary greater than 50000, or employees with a salary greater than 60000:

SELECT first_name, last_name, email
FROM employees
WHERE (department_id = 1 AND salary > 50000) OR salary > 60000;

Output

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

Example with NOT

To retrieve employees not in department 2 or with a salary greater than 50000:

SELECT first_name, last_name, email
FROM employees
WHERE NOT department_id = 2 OR salary > 50000;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Arjun	Singh	arjun.singh@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

Example with IN

To retrieve employees in departments 1 or 3 with a salary greater than 50000:

SELECT first_name, last_name, email
FROM employees
WHERE department_id IN (1, 3) OR salary > 50000;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

Example with BETWEEN

To retrieve employees with a salary between 50000 and 60000 or in department 2:

SELECT first_name, last_name, email
FROM employees
WHERE salary BETWEEN 50000 AND 60000 OR department_id = 2;

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

Conclusion

The OR operator is a fundamental tool for combining multiple conditions in SQL queries. This chapter covered the basic syntax, combining multiple conditions, and using the OR operator with other SQL operators. Understanding how to use the OR operator effectively will enhance your ability to query and analyze your database data.