SQL OR Operator: A Guide with Examples

Syntax of the SQL OR Operator

The OR operator is simple to use and incorporate in SQL queries. Below is the basic syntax when used in the SELECT statement.

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

Note that you can use more than one OR condition in the WHERE clause.

Basic usage in the SELECT statement

The OR operator is used in the SELECT statement to return records that meet any of the conditions provided. This makes the OR operator useful when retrieving records without writing complex multiple queries.

In the example below, the OR operator filters all employee records for employees in "New York" or "Los Angeles."

-- Select employeees from New York or Los Angeles city
SELECT first_name, last_name, city
FROM employees
WHERE city = 'New York'
OR city = 'Los Angeles';

Combining OR with AND in SQL

The OR operator can be combined with the AND operator to form more complex conditions in a SQL query. When using both the OR operator and the AND operator, we use parentheses to control the order of the operations.

Consider the following query: We want to select all employees in the IT department and who live in "New York" (as one condition), or who earn more than $70,000. The parentheses group the AND condition to evaluate as a single unit before applying the OR operator.

-- Select columns
SELECT first_name, last_name, department, city, salary
FROM employees
 -- Grouping IT department in New York
WHERE (department = 'IT' AND city = 'New York')
-- Employees with salary over 70,000
OR salary > 70000;

Example output of table filtered by combining OR with AND operator. Image by Author.

The above query evaluates and returns records of:

• Employees who work in the IT department and live in New York.

• Or any employee earning over $70,000, regardless of department or city.

When combining the OR operator with the AND operator, you should correctly group the conditions with the parentheses. This mistake might lead to logical errors and return unintended results.

If the parentheses are not used in the query, the AND condition will be evaluated before the OR condition. Thus, using the parentheses correctly will explicitly group the condition and ensure the intended order of evaluation is followed.

Examples of the SQL OR Operator in Action

The OR operator can be used in different SQL queries to retrieve records or manipulate database data. The following are examples of the use cases of the OR operator.

Filtering data with multiple conditions

The OR operator is mainly used to filter records based on two or more conditions. This method is important when broadening the database's search criteria. The following example will filter and return records showing employees from New York City or earning over $80,000.

-- Select columns
SELECT first_name, last_name, department, city, salary
FROM employees
 -- Employees in New York City
WHERE city = 'New York'
-- Employees with salary over 80,000
OR salary > 80000;

Using the OR operator in INSERT statements

You can use the OR operator with INSERT statements to insert new records in the table if it meets the specified condition. In the example below, the query will insert a new record into the employees table if the employee is either in the HR department or if the employee earns more than $60,000. The query below is consistent for SQL Server, MySQL, and PostgreSQL databases.

-- Insert a new record into the 'employees' table
INSERT INTO employees (employee_id, first_name, last_name, department, salary, city)
SELECT 49, 'Anna', 'Johnson', 'HR', 62000, 'New York'
 -- First condition: department is 'HR'
WHERE 'HR' = 'HR' 
 -- Second condition: salary is greater than $60,000
   OR 62000 > 60000;

In the Oracle database, the INSERT statement is different and requires a FROM clause using the dual table, a special one-row, one-column table provided in the database. Take our Introduction to SQL Server course if you will be working with SQL Server specifically.  

-- Insert a new record into the 'employees' table
INSERT INTO employees (employee_id, first_name, last_name, department, salary, city)
SELECT 49, 'Anna', 'Johnson', 'HR', 62000, 'New York'
FROM dual
WHERE 'HR' = 'HR'  -- First condition: department is 'HR'
   OR 62000 > 60000;  -- Second condition: salary is greater than $60,000

Using the OR operator in the UPDATE statement

The UPDATE statement is used in SQL to modify existing records in a table. You can use the OR operator in the UPDATE statement to modify these records according to multiple conditions, to modify only those meeting the conditions.

In the example below, we use the OR operator to update the employee's salary if they are in the Finance department or live in Chicago.

-- Update the salary for the employee
UPDATE employees
-- Increase salary by 10%
SET salary = salary * 1.1  
-- First condition: department is 'Finance'
WHERE department = 'Finance' 
 -- Second condition: employees in Chicago City
OR city = 'Chicago';

Using the OR operator in the DELETE statement

Similarly, the DELETE statement in SQL is used to remove existing records from a table. You can use the OR operator with the DELETE statement to remove records from a table according to specific conditions.

The query below will remove records from the employees table where the employee is from Chicago or earns $60,000.

-- Delete records from the 'employees' table
DELETE FROM employees
-- First condition: employees from Chicago
WHERE city = 'Chicago'
-- Second condition: employees earning 60000
OR salary = 60000;

Performance Considerations of SQL OR Queries

When working with large datasets, you may experience performance issues with the OR operator. This issue might result in slowed operations when filtering records from large datasets. The following are the common performance issues and how to mitigate them.

Full table scans

When using the OR operator in large datasets, it performs full table scans, especially when no proper indexing is used. This issue can slow query execution time since the query evaluates the table by each row. To avoid this issue, you should index the required columns so the database engine can locate the columns for easier and faster table scan.

Complex condition evaluation

When using the OR operator to evaluate multiple conditions in a table, the query may become significantly slower, especially when it is not properly indexed. When using complex OR conditions, restructuring the query into smaller parts, such as using subqueries, may help improve query performance.

Redundant conditions

Including unnecessary OR conditions may slow the query performance when using the OR operator since it must evaluate all the conditions. Using only the necessary OR conditions and those that are likely true will help simplify and improve the query performance.

Alternatives to the OR operator

When facing performance issues with the OR operator, you can consider the following alternatives to filter data based on multiple conditions.

Using the UNION operator

The UNION operator is mainly used to combine results from two or more SELECT statements when different indexing is required. In the query below, the UNION operator combines the operations to filter and return the records of employees from the IT department of New York city. Read our tutorial, UNION vs UNION ALL in SQL if you are not yet familiar with UNION. 

-- Use UNION instead of OR for better performance
SELECT first_name, last_name, department, city
FROM employees
-- First condition
WHERE department = 'IT'  
UNION
SELECT first_name, last_name, department, city
FROM employees
-- Second condition
WHERE city = 'New York'; 

Using CASE statements

The CASE statement is an important alternative to the OR operator if you want to include logical conditions in the query. The CASE statement improves query performance according to its optimal execution. The query below uses the CASE statement to categorize departments based on multiple conditions.

-- Select first name, last name, and a derived category based on conditions
SELECT first_name, last_name,  
-- Use CASE to determine the category based on department or city
CASE  
-- If department is 'IT', categorize as 'Tech' If city is 'New York', categorize as 'NYC'
  WHEN department = 'IT' THEN 'Tech'  
  WHEN city = 'New York' THEN 'NYC'  -- 
  ELSE 'Other'
END AS category
FROM employees; 

Check out our SQL Fundamentals skill track to learn more about the UNION operator and CASE statement for data manipulation.

Conclusion

The OR operator is a versatile SQL operator for building flexible and complex queries. The OR operator allows you to filter table records based on multiple conditions. As we have seen, when used with other statements such as the INSERT, UPDATE, and DELETE statements, the OR operator also allows for modifying records in tables. As a data analyst, it is important to understand the performance issues with the OR operator and alternative methods, such as the UNION operator and CASE WHEN statements. 

I encourage you to practice using the OR operator to filter records with different datasets to become more comfortable with its functionality. To advance your SQL skills, consider our Associate Data Analyst in SQL career track to become a proficient analyst. The Reporting in SQL course will also help you learn how to analyze and present data using dashboards. Finally, I recommend obtaining DataCamp’s SQL Associate Certification to demonstrate your skills and proficiency in SQL for data analysis.