SQLite OR Operator

The SQLite OR logical operator is an important tool for filtering data in SQLite. OR allows returning rows that match ANY of the specified conditions.

For example, to select users who are either 20 or 30 years old from a users table:

SELECT * FROM users
WHERE age = 20 OR age = 30;

This article provides a comprehensive guide to using OR for writing more powerful SQLite queries.

SQLite OR Operator in a Nutshell

The OR operator combines multiple conditions where rows satisfying ANY condition should be included in the result set.

Basic syntax of SQLite OR

WHERE condition1 OR condition2 OR condition3...

If condition1, condition2 or condition3 is true for a given row, that row is returned. The conditions are evaluated from left to right.

See this SQLite AND operator guide for differences between OR and AND.

Practical Examples: SQLite OR in SELECT

Filtering by single condition

Select users who are under 18 or over 30:

SELECT * FROM users
WHERE age < 18 OR age > 30;

Multiple OR conditions

Select users who are under 20 OR over 25:

SELECT * FROM users
WHERE age < 20 OR age > 25;

Real-world example: Customer data

Filter customers from California OR New York with a registration date after 1/1/2022:

SELECT * FROM customers
WHERE state = 'CA' OR state = 'NY' 
   AND registration_date > '2022-01-01';

SQLite OR in UPDATE and DELETE

OR conditions can also be used when updating or deleting rows:

Updating with OR

Update customers from CA or NY setting active=1:

UPDATE customers
SET active = 1
WHERE state = 'CA' OR state = 'NY'; 

Deleting with OR

Delete users under 18 or over 30 years old:

DELETE FROM users
WHERE age < 18 OR age > 30;

Real-world example: Inventory

Delete products with 0 stock OR expiration within 7 days:

DELETE FROM inventory
WHERE stock = 0 OR expiration < DATE('now', '+7 days');

Combining SQLite OR with AND

OR and AND can be combined by using parentheses:

WHERE (condition1 OR condition2) 
  AND (condition3 OR condition4)

The AND takes higher precedence than the SQLite OR.

Employee data filtering

Select employees in the engineering or sales department with a join date after 1/1/2021:

SELECT * FROM employees
WHERE (department = 'engineering' OR department = 'sales')
  AND join_date > '2021-01-01';

SQLite OR in Nested Queries

SQLite OR can be used in subqueries and nested queries.

Sales data analysis

Count customers who made >100 purchases OR spent >$5000:

SELECT COUNT(*) FROM customers 
WHERE customer_id IN
  (SELECT customer_id FROM orders
   WHERE num_orders > 100 OR total_spent > 5000);

Performance Considerations for SQLite OR

• Simple OR conditions are fast, but complex ones with many alternates can get slow
• Reduce number of OR conditions if speed is critical
• Index columns used in OR filters for faster lookups
• Test and optimize slow queries involving OR

Frequently Asked Questions

Conclusion

The OR operator is invaluable for filtering SQLite data. This guide covers the key concepts, use cases and best practices for harnessing the power of OR in your queries.

Proper use of OR along with AND and other operators will enable you to build robust data pipelines.