MySQL OR Operator

MySQL OR Operator

The OR operator in MySQL is used to combine two or more conditions in a SQL query. It returns results when at least one of the conditions evaluates to TRUE. The OR operator is commonly used in the WHERE, HAVING, and ON clauses of a query.

Syntax

condition1 OR condition2 OR condition3 ...

• condition1, condition2, ...: These are the logical conditions to evaluate.
• The result is TRUE if any of the conditions is TRUE.
• If all conditions are FALSE, the result is FALSE.

Examples

1. Using OR in a WHERE Clause

This query retrieves employees whose department is either HR or Finance.

SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id = 1 OR department_id = 2;

Result:

employee_id	first_name	department_id
101	John	1
102	Sarah	2

2. Using OR with Multiple Conditions

This query fetches products that have either a price greater than 100 or a stock less than 50.

SELECT product_name, price, stock
FROM products
WHERE price > 100 OR stock < 50;

Result:

product_name	price	stock
Product A	150	40
Product B	90	30

3. Combining OR and AND

You can combine the OR operator with the AND operator for more complex conditions. Use parentheses to clarify precedence.

SELECT * 
FROM orders
WHERE (order_status = 'Completed' AND order_date > '2025-01-01') 
   OR (order_status = 'Pending' AND total_amount > 500);

Explanation:

• The query selects:
  1. Completed orders placed after January 1, 2025.
  2. Pending orders with a total amount greater than 500.

4. Using OR in a HAVING Clause

This query groups orders by customer and filters results where the total amount is greater than 1000 or the total number of orders is greater than 10.

SELECT customer_id, COUNT(order_id) AS total_orders, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000 OR COUNT(order_id) > 10;

5. OR in a JOIN Condition

The OR operator can also be used in the ON clause of a JOIN statement.

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id 
   OR e.manager_id = d.manager_id;

Behavior with NULL

• If any condition evaluates to NULL, the result depends on the other conditions:
  • If another condition evaluates to TRUE, the overall result is TRUE.
  • If all other conditions are FALSE, the result is NULL (treated as FALSE in practical queries).

Example:

SELECT 1 OR NULL; -- Returns 1 (TRUE)
SELECT NULL OR 0; -- Returns NULL (FALSE in queries)

Performance Tips

1. Indexing:
   • MySQL might not efficiently use indexes when OR is used with non-indexed columns. Consider using UNION instead for better performance in such cases.
   • Example:

     SELECT * FROM table1 WHERE column1 = value1
     UNION
     SELECT * FROM table1 WHERE column2 = value2;
     

2. Parentheses:
   • Use parentheses to ensure correct operator precedence, especially when combining OR and AND.

Practical Use Cases

1. Fetching data based on alternative conditions (e.g., multiple status values or categories).
2. Combining filters for dynamic search queries.
3. Creating flexible query logic in user-driven applications.

Let me know if you'd like additional examples or clarifications!