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.
What's in this article
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
Using multiple OR conditions?
List all conditions separated by OR:
WHERE condition1 OR condition2 OR condition3…
What are SQLite OR limitations?
No major limitations, but complex OR queries can get slow. Optimize with indexes if needed.
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.