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

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

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

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

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

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

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.