OR

• The OR operator in Oracle SQL is a logical operator that is used to combine multiple conditions in a WHERE clause. 
• When you use OR, it allows you to select rows that meet either of the conditions you specify, rather than requiring all conditions to be true (as with AND).
• The OR operator is used to combine two or more conditions.
• A row is included in the result set if at least one of the conditions evaluates to TRUE.

How OR Works

• The OR operator returns a row if any of the conditions connected by OR are TRUE.
• If one condition is TRUE, the row is included, even if the other conditions are FALSE.
• All conditions connected by OR do not need to be true at the same time; as long as one is true, the row will be included in the result.

 

Example 1: Basic OR Example

Suppose you have an employees table, and you want to find employees who work in the Sales department or those who have a salary greater than 6000.

SELECT employee_id, first_name, department, salary

FROM employees

WHERE department = 'Sales'

OR salary > 6000;

In this query:

• The first condition checks if the employee is in the Sales department.
• The second condition checks if the employee’s salary is greater than 6000.
• The row will be returned if either of the conditions is TRUE.
  

Example 2: Using OR with Multiple Conditions

• Suppose you want to find all employees in the Sales department, employees whose salary is above 7000, or employees hired after January 1, 2020:

SELECT employee_id, first_name, department, salary, hire_date

FROM employees

WHERE department = 'Sales'

OR salary > 7000

OR hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');

In this query:

• The row will be returned if the employee satisfies any one of the conditions.

4. Combining OR with AND

When you combine OR with AND, it’s important to understand the order of evaluation. AND has higher precedence than OR. If you want to control the order of operations, you can use parentheses.

Example 1: Using OR and AND Together

Suppose you want to find employees who are either in the Sales department or those with a salary greater than 5000 and hired after 2020:

SELECT employee_id, first_name, department, salary, hire_date

FROM employees

WHERE department = 'Sales'

OR (salary > 5000 AND hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD'));

In this query:

• The first condition checks if the department is Sales.
• The second condition, wrapped in parentheses, checks if the employee has a salary greater than 5000 and was hired after January 1, 2020.
• Parentheses are crucial to ensure the AND condition is evaluated before the OR condition.

Example 2: OR with Multiple AND Conditions

SELECT employee_id, first_name, department, salary, hire_date

FROM employees

WHERE (department = 'Sales' AND salary > 5000)

OR (department = 'Marketing' AND hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD'));

Here, the query checks for employees who are in the Sales department with a salary above 5000 or employees in the Marketing department who were hired after January 1, 2020.

5. Order of Precedence in OR

The order of evaluation is important when using multiple logical operators. OR has lower precedence than AND, so AND conditions are evaluated first unless you use parentheses to change the evaluation order.

For example:

SELECT employee_id, first_name, department, salary

FROM employees

WHERE department = 'Sales'

OR department = 'Marketing' AND salary > 5000;

This query is evaluated as:

SELECT employee_id, first_name, department, salary

FROM employees

WHERE department = 'Sales'

OR (department = 'Marketing' AND salary > 5000);

This means the query will return employees who are in the Sales department or those who are in the Marketing department and have a salary greater than 5000.

6. Using OR with IN, BETWEEN, LIKE, and IS NULL

You can use OR in combination with other SQL operators like IN, BETWEEN, LIKE, and IS NULL:

Example 1: OR with IN

SELECT employee_id, first_name, department

FROM employees

WHERE department IN ('Sales', 'Marketing')

OR salary > 5000;

This query retrieves employees who are in either the Sales or Marketing departments or those whose salary is greater than 5000.

Example 2: OR with BETWEEN

SELECT employee_id, first_name, department, salary

FROM employees

WHERE department = 'Sales'

OR salary BETWEEN 4000 AND 7000;

This query retrieves employees from the Sales department or those with a salary between 4000 and 7000.

Example 3: OR with LIKE

SELECT employee_id, first_name, department

FROM employees

WHERE first_name LIKE 'J%'

OR department = 'Sales';

This query retrieves employees whose first name starts with "J" or those who work in the Sales department.

Example 4: OR with IS NULL

SELECT employee_id, first_name, department

FROM employees

WHERE hire_date IS NULL

OR department = 'HR';

This query retrieves employees who either have a NULL hire date or are in the HR department.

7. Performance Considerations

• Indexes: If the columns involved in OR conditions are indexed, Oracle can use the indexes to speed up the query. However, if you have multiple OR conditions, Oracle might not be able to effectively use indexes, especially if the conditions involve different columns.
• Complex Queries: Using OR with multiple conditions can sometimes result in slower performance on large datasets, as it requires checking multiple conditions for each row. It's advisable to test the performance of complex queries using OR and consider query optimization techniques.

8. Common Pitfalls When Using OR

a. Returning Too Many Rows

Since OR allows a row to match if it satisfies any of the conditions, it can sometimes return more rows than expected, especially if the conditions are too broad.

For example:

SELECT * FROM employees

WHERE department = 'Sales'

OR department = 'Marketing';

This query will return all employees from both the Sales and Marketing departments, which may not always be the intended result.

b. Incorrect Parentheses Usage

Improper use of parentheses when combining OR with AND can lead to unexpected results due to the precedence rules of logical operators.

For example:

SELECT * FROM employees

WHERE department = 'Sales'

AND department = 'Marketing'

OR salary > 5000;

Without parentheses, this query may not work as expected, as it will be interpreted as:

SELECT * FROM employees

WHERE (department = 'Sales' AND department = 'Marketing')

OR salary > 5000;

This would return all employees whose salary is greater than 5000, regardless of the department.

9. OR with NULL Values

• OR can be tricky when working with NULL values because NULL is not equal to any value, including NULL. Be careful when using OR with IS NULL or other conditions that may involve NULL.

Example:

SELECT employee_id, first_name, department, hire_date

FROM employees

WHERE hire_date IS NULL

OR department = 'Sales';

This query will return employees whose hire date is NULL or those in the Sales department.