Combining Conditions with SQL OR
In SQL, the OR operator is used to combine two or more conditions in a query, allowing rows to be selected if any of the conditions are true. This makes the OR operator useful when you want to retrieve data that meets one of several possible conditions. Unlike the AND operator, where all conditions must be true, with OR, only one condition needs to be true for the row to be returned.
In this chapter, we will dive deep into how the OR operator works, how it can be used in combination with other SQL operators, and advanced techniques for optimizing your queries. We will cover everything from the basics to complex examples, ensuring that you fully understand how to use OR effectively in SQL.
The Basics of the OR Operator
The OR operator is used in the WHERE clause to filter rows based on multiple conditions. If any of the conditions combined with OR is true, the row will be returned in the result set.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
condition1, condition2, condition3: These are logical expressions that can evaluate to true or false. If at least one of the conditions evaluates to true, the row is included in the result.
Example 1: Basic OR Usage
Consider the following employees table:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | Bob Brown | IT | $7000 |
| 4 | Mary White | HR | $5000 |
| 5 | David Green | Sales | $5500 |
Query:
SELECT name, department
FROM employees
WHERE department = 'IT' OR salary > 5500;
Output:
| Name | Department |
|---|---|
| Jane Smith | IT |
| Bob Brown | IT |
| Bob Brown | IT |
| David Green | Sales |
- The query returns rows where the department is
ITor the salary is greater than 5500. Jane SmithandBob Brownwork inIT, whileDavid Greenhas a salary greater than 5500.
Combining Multiple Conditions with OR
You can combine more than two conditions using OR. The more conditions you include, the broader the result set becomes, since a row will be returned if any of the conditions is true.
Example 2: Multiple OR Conditions
Let’s find employees from either the IT or HR department, or those whose salary is greater than 5500.
Query:
SELECT name, department, salary
FROM employees
WHERE department = 'IT' OR department = 'HR' OR salary > 5500;
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Jane Smith | IT | $6000 |
| Bob Brown | IT | $7000 |
| Mary White | HR | $5000 |
| David Green | Sales | $5500 |
The query returns all employees who work in the IT or HR departments, or those whose salary is greater than 5500.
Operator Precedence with OR
In SQL, the AND and OR operators follow specific rules for precedence (order of evaluation). The AND operator has higher precedence than OR. This means that conditions connected by AND are evaluated first, unless parentheses are used to explicitly define the order.
Example 3: OR and AND Precedence
Consider a query where we want to retrieve employees who are in the IT department and earn more than 6000 or employees who work in the HR department.
Query:
SELECT name, department, salary
FROM employees
WHERE department = 'IT' AND salary > 6000 OR department = 'HR';
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Bob Brown | IT | $7000 |
| Mary White | HR | $5000 |
- Since
ANDhas a higher precedence, the conditiondepartment = 'IT' AND salary > 6000is evaluated first. - Then, the result is combined with the
ORconditiondepartment = 'HR'. - Therefore, employees in
HRand those inITearning more than 6000 are returned.
Using Parentheses to Control Precedence
To ensure the query logic is executed in the intended order, parentheses can be used to explicitly group conditions. This is particularly useful when combining AND and OR operators.
Example 4: Grouping Conditions with Parentheses
Let’s adjust the previous query so that we retrieve employees who are either in the IT department with a salary greater than 6000 or work in the HR department, regardless of salary.
Query:
SELECT name, department, salary
FROM employees
WHERE (department = 'IT' AND salary > 6000) OR department = 'HR';
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Bob Brown | IT | $7000 |
| Mary White | HR | $5000 |
By using parentheses, we control the evaluation order, ensuring that the conditions for IT employees are grouped together before applying the OR to HR employees.
Advanced Usage of OR with Other Operators
The OR operator can be combined with other SQL comparison and logical operators like BETWEEN, IN, LIKE, and IS NULL to build more complex conditions.
Using OR with BETWEEN
The BETWEEN operator is used to filter data within a specified range. You can combine it with OR to specify multiple ranges.
Example 5: OR with BETWEEN
Let’s find employees with a salary between 5000 and 6000, or those between 6500 and 7000.
SELECT name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 6000 OR salary BETWEEN 6500 AND 7000;
Output:
| Name | Salary |
|---|---|
| John Doe | $5000 |
| Jane Smith | $6000 |
| Bob Brown | $7000 |
The query returns employees whose salaries fall within the two specified ranges.
Using OR with IN
The IN operator allows you to specify multiple values. Combined with OR, it can be used to filter rows based on multiple criteria.
Example 6: OR with IN
Let’s find employees in either the IT or HR departments, or those in the Sales department with a salary of 5500.
SELECT name, department, salary
FROM employees
WHERE department IN ('IT', 'HR') OR (department = 'Sales' AND salary = 5500);
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Jane Smith | IT | $6000 |
| Bob Brown | IT | $7000 |
| Mary White | HR | $5000 |
| David Green | Sales | $5500 |
The IN operator is used to filter IT and HR employees, while the OR operator ensures that Sales employees with a specific salary are also included.
Using OR with LIKE
The LIKE operator is used for pattern matching. When combined with OR, you can search for multiple patterns in your data.
Example 7: OR with LIKE
Let’s find employees whose names start with either ‘J’ or ‘D’.
SELECT name, department
FROM employees
WHERE name LIKE 'J%' OR name LIKE 'D%';
Output:
| Name | Department |
|---|---|
| John Doe | HR |
| Jane Smith | IT |
| David Green | Sales |
The query returns employees whose names start with either J or D.
Handling NULL Values with OR
In SQL, NULL represents missing or unknown data. When using OR with NULL values, you must explicitly handle the condition, as NULL is not equal to any value, not even NULL itself.
Example 8: Using OR with IS NULL
Let’s extend our employees table to include NULL values in the department column:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | Bob Brown | IT | $7000 |
| 4 | Mary White | NULL | $5000 |
| 5 | David Green | Sales | $5500 |
Mary White does not have a department assigned, represented by NULL. Now, we want to retrieve employees who either work in the IT department or have no department assigned (NULL).
Query:
SELECT name, department
FROM employees
WHERE department = 'IT' OR department IS NULL;
Output:
| Name | Department |
|---|---|
| Jane Smith | IT |
| Bob Brown | IT |
| Mary White | NULL |
- The query returns employees in the
ITdepartment or with aNULLvalue in the department column. - Notice that we had to use
IS NULLexplicitly because a simpledepartment = NULLwould not work in SQL, asNULLcannot be compared with=.
The OR operator in SQL is a powerful tool for filtering data based on multiple conditions where at least one condition must be true. This chapter covered the basics of how OR works, its combination with other SQL operators, handling NULL values, and performance optimization techniques. With this knowledge, you can now confidently use the OR operator to write flexible, powerful queries that filter data according to multiple conditions, making your SQL skills more advanced and efficient.Happy coding !❤️