The AND and OR Operators in SQLite: A Complete Guide

Almost every meaningful query I write involves more than one condition. “Give me all orders that are pending AND placed in the last 30 days.” “Find customers who are in California OR New York.” Once you move past the simplest single-condition WHERE clause, you need a way to combine multiple conditions logically, and that’s exactly what AND and OR give you. They look simple on the surface, but the way they interact — especially when mixed together — is a genuine source of bugs if you don’t fully understand operator precedence and short-circuit behavior. Let’s go through it thoroughly.

What AND and OR Do

AND and OR are logical operators used to combine two or more boolean conditions in a WHERE clause (or HAVING, or a CASE expression, or really anywhere a boolean expression is valid).

• AND returns true only if all the conditions it connects are true.
• OR returns true if at least one of the conditions it connects is true.

The basic syntax:

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

Basic AND Examples

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering' AND salary > 80000;

This returns only employees who satisfy both conditions simultaneously — they must be in Engineering, and their salary must exceed 80,000. If either condition fails for a given row, that row is excluded entirely.

You can chain more than two conditions with AND:

SELECT * FROM orders
WHERE status = 'completed'
  AND order_date >= '2024-01-01'
  AND total_amount > 100;

Every single condition here must be true for a row to be included in the results.

Basic OR Examples

SELECT name, department
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

This returns employees who are in either department — a row only needs to satisfy one of the two conditions to be included.

SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Appliances' OR category = 'Furniture';

With three or more conditions, this pattern gets verbose fast — later in this guide I’ll cover using IN as a cleaner alternative for exactly this kind of “match any of these values” scenario.

Combining AND and OR — Where It Gets Tricky

This is the part that trips up even experienced developers occasionally: when you mix AND and OR in the same WHERE clause without parentheses, SQLite (like standard SQL generally) evaluates AND with higher precedence than OR. That means AND conditions are grouped together first, before OR is applied.

Consider this query:

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 90000;

At first glance, you might read this as: “employees in Sales or Marketing, who also earn more than 90,000.” But that’s NOT what this query actually does. Because AND binds tighter than OR, SQLite actually evaluates it as:

WHERE department = 'Sales' OR (department = 'Marketing' AND salary > 90000)

This means every single Sales employee gets included, regardless of their salary — the salary condition only applies to the Marketing branch. If your intention was for the salary filter to apply to both departments, this query has a real bug, and it’s the kind of bug that’s easy to miss because the query still “runs” and returns plausible-looking results.

Always Use Parentheses When Mixing AND and OR

The fix — and honestly, my personal rule that I never break — is to always use explicit parentheses whenever AND and OR appear together in the same clause, even when I’m confident I know what the default precedence would produce. It removes all ambiguity, both for SQLite’s parser and, more importantly, for the next person (often me, six months later) reading the query.

If I actually wanted “Sales or Marketing, both with salary over 90,000”:

SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 90000;

And if I actually wanted the original (unintended) behavior — all of Sales, plus Marketing employees earning over 90,000 — I’d still write it with explicit parentheses to make the intent unmistakable:

SELECT * FROM employees
WHERE department = 'Sales' OR (department = 'Marketing' AND salary > 90000);

Both versions are functionally different, and both are now completely unambiguous to read. That’s the whole point.

NOT with AND and OR

The NOT operator negates a condition, and it also has its own precedence rules relative to AND and OR. NOT binds more tightly than both AND and OR, meaning it applies to the condition immediately following it before any AND/OR logic is evaluated.

SELECT * FROM employees
WHERE NOT department = 'Sales' AND salary > 50000;

This evaluates as (NOT department = 'Sales') AND (salary > 50000) — employees not in Sales, who also earn over 50,000.

Applying De Morgan’s laws can also help simplify or double-check complex negated conditions:

-- These two are logically equivalent:
WHERE NOT (department = 'Sales' AND salary > 50000);
WHERE department != 'Sales' OR salary <= 50000;

I find it genuinely useful to mentally (or literally, on paper) rewrite a complicated negated boolean expression using De Morgan’s laws whenever I’m unsure whether my NOT logic is behaving the way I expect.

AND, OR, and NULL — The Three-Valued Logic Problem

This is a subtlety that catches nearly everyone eventually. SQL doesn’t use simple two-valued (true/false) boolean logic — it uses three-valued logic, where any comparison involving NULL evaluates to NULL (meaning “unknown”), not true or false.

This matters enormously when NULL values interact with AND and OR:

-- If salary is NULL, this entire condition evaluates to NULL, and the row is excluded
WHERE salary > 50000

-- AND: if either side is NULL and the other side is TRUE, the result is NULL (excluded)
--       if either side is NULL and the other side is FALSE, the result is FALSE (excluded)
WHERE department = 'Sales' AND salary > 50000  -- excluded if salary is NULL, regardless of department

-- OR: if either side is NULL and the other side is TRUE, the result is TRUE (included)
--     if either side is NULL and the other side is FALSE, the result is NULL (excluded)
WHERE department = 'Sales' OR salary > 50000  -- included if department is 'Sales', even if salary is NULL

The practical takeaway: whenever a column involved in your AND/OR logic might contain NULL, and you need those rows to be included or excluded predictably, you need to handle that explicitly with IS NULL or IS NOT NULL, rather than assuming NULL will just “count as false.”

SELECT * FROM employees
WHERE department = 'Sales' AND (salary > 50000 OR salary IS NULL);

This explicitly includes Sales employees with either a salary over 50,000 or a missing salary value, rather than silently excluding the NULL-salary rows through the default three-valued logic behavior.

Using IN as a Cleaner Alternative to Repeated OR

When you find yourself chaining many OR conditions against the same column, the IN operator is almost always cleaner and easier to read:

-- Verbose OR chain
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Appliances' OR category = 'Furniture';

-- Cleaner equivalent using IN
SELECT * FROM products
WHERE category IN ('Electronics', 'Appliances', 'Furniture');

These two queries are functionally identical, but the IN version is far more readable, especially as the list of possible values grows. I switch to IN as soon as I have three or more OR conditions against the same column.

Using BETWEEN as a Cleaner Alternative to AND for Ranges

Similarly, a common AND pattern is checking whether a value falls within a range — and BETWEEN often expresses that more cleanly:

-- AND-based range check
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date <= '2024-12-31';

-- Cleaner equivalent using BETWEEN
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

Worth noting: BETWEEN is inclusive on both ends, matching the AND version I wrote above exactly. If you need an exclusive range on either end, you’re back to writing it out explicitly with AND and the appropriate comparison operators.

Combining AND/OR with Other Operators

AND and OR combine naturally with LIKE, GLOB, IN, BETWEEN, IS NULL, and pretty much any other boolean-producing expression in SQLite.

SELECT * FROM customers
WHERE (email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com')
  AND signup_date >= '2024-01-01'
  AND is_active = 1;

This finds active customers who signed up in 2024 or later, using either a Gmail or Yahoo email address. Notice, again, the deliberate parentheses around the OR condition — this is exactly the pattern I described earlier, and it’s essential here because without the parentheses, the AND conditions would bind to the Yahoo branch only, not both email conditions.

AND/OR in JOIN Conditions

AND and OR aren’t limited to WHERE clauses — they’re just as valid inside JOIN conditions, though the implications differ slightly depending on join type.

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id AND c.is_active = 1;

Placing the c.is_active = 1 condition in the JOIN’s ON clause (rather than in a WHERE clause) matters specifically for LEFT JOINs, because it changes whether unmatched rows from the left table are still included. This is a common and genuinely important gotcha — putting a filter condition on the right-hand table in WHERE versus ON produces different results for outer joins, and it’s worth understanding deliberately rather than by accident.

AND/OR in CASE Expressions

You’ll also see AND and OR used constantly inside CASE expressions, to build more complex conditional logic within a single computed column.

SELECT name, salary,
    CASE
        WHEN department = 'Engineering' AND salary > 100000 THEN 'Senior Engineer Tier'
        WHEN department = 'Sales' OR department = 'Marketing' THEN 'Revenue Team'
        ELSE 'Other'
    END AS tier
FROM employees;

Common Mistakes to Avoid

1. Mixing AND and OR without parentheses, unintentionally changing the logical grouping due to AND’s higher precedence.
2. Forgetting that NULL breaks simple true/false assumptions in both AND and OR expressions, silently excluding rows you expected to be included (or vice versa).
3. Chaining long OR lists against the same column instead of using the cleaner, more readable IN operator.
4. Placing filter conditions on the wrong side of a JOIN (ON vs. WHERE) when working with LEFT JOINs, changing which rows are ultimately included.
5. Assuming NOT distributes the way you expect without double-checking with De Morgan’s laws when negating a compound AND/OR expression.

Best Practices

• Always use explicit parentheses when AND and OR appear together in the same expression — never rely on implicit precedence, even when you’re confident about the outcome.
• Explicitly handle NULL with IS NULL / IS NOT NULL whenever a column involved in your logic might contain NULL and its presence needs a specific, intentional outcome.
• Switch to IN once you have three or more OR conditions comparing the same column to different literal values.
• Switch to BETWEEN for simple inclusive range checks instead of writing out two AND’d comparisons.
• Be deliberate about placing conditions in ON vs. WHERE when working with outer joins, since the two are not interchangeable.
• When debugging a complex boolean expression, break it into smaller pieces and test them individually with simple SELECT statements before combining them back together.

AND and OR are two of the very first things anyone learns in SQL, and yet they’re also two of the operators most likely to produce subtle, silent bugs when combined carelessly. Take the extra ten seconds to add parentheses and think through NULL handling — it’s one of the highest-value habits you can build as a SQLite developer.

Frequently Asked Questions

Are AND and OR short-circuit operators in SQLite? SQLite doesn’t guarantee left-to-right short-circuit evaluation the way general-purpose programming languages typically do. While it often can skip evaluating the second operand once the first determines the outcome (e.g., an AND with a false left side), you shouldn’t structure queries assuming a specific evaluation order for side-effecting expressions, since SQL expressions are meant to be side-effect-free in the first place. Functions like RANDOM() inside a WHERE clause built from AND/OR conditions can behave in ways that feel surprising if you’re mentally modeling this like short-circuit evaluation in a language such as Python or JavaScript.

Can I use AND/OR outside of a WHERE clause? Yes — AND and OR are general boolean expression operators and can appear in HAVING, JOIN ON conditions, CASE expressions, CHECK constraints, and trigger WHEN clauses, not just in WHERE.

CREATE TABLE employees (
    salary REAL,
    bonus REAL,
    CHECK (salary > 0 AND bonus >= 0)
);

What’s the difference between OR and the IN operator? For comparing a single column against multiple literal values, IN is functionally equivalent to a chain of OR’d equality checks, just more concise and readable. But OR is more general — it can combine entirely different conditions (different columns, different comparison types), whereas IN is specifically for checking membership of one value against a set.

-- Equivalent
WHERE status = 'pending' OR status = 'processing' OR status = 'shipped';
WHERE status IN ('pending', 'processing', 'shipped');

-- Not expressible with IN alone — different columns and operators involved
WHERE status = 'pending' OR total_amount > 1000;

Does the order of AND conditions matter for performance? Generally no — SQLite’s query planner reorders and optimizes conditions based on available indexes and statistics, regardless of the order you wrote them in. Write your conditions in whatever order is most readable to a human, and trust the query planner (verified with EXPLAIN QUERY PLAN when performance genuinely matters) to handle the execution details.

AND/OR in Real-World Filtering Scenarios

Let me walk through a few genuinely realistic filtering scenarios that combine everything covered in this guide, since seeing the full pattern in context tends to make the precedence and NULL-handling rules click more solidly.

-- Find high-value, at-risk customers: either high total spend with no recent activity,
-- or a specific manual flag set by support staff
SELECT * FROM customers
WHERE (
    (total_spend > 5000 AND last_order_date < date('now', '-180 days'))
    OR flagged_at_risk = 1
);

-- Find orders that need attention: unpaid and overdue, or explicitly marked urgent,
-- but exclude anything already cancelled
SELECT * FROM orders
WHERE status != 'cancelled'
  AND (
        (payment_status = 'unpaid' AND due_date < date('now'))
        OR is_urgent = 1
      );

Notice in both examples how the parentheses aren’t just stylistic — they’re doing real logical work, grouping the OR condition together before it interacts with the surrounding AND conditions. Writing these same queries without the parentheses would silently change their meaning, due to AND’s higher precedence over OR.

AND/OR with Multiple NULL-able Columns

A more advanced NULL-handling scenario worth walking through: when multiple columns in a compound condition can independently be NULL, the resulting three-valued logic can compound in ways that are genuinely hard to reason about without careful thought.

SELECT * FROM leads
WHERE (email IS NOT NULL AND phone IS NOT NULL)
   OR (email IS NOT NULL AND company IS NOT NULL);

This finds leads that have enough contact information to be actionable — either email plus phone, or email plus company — while correctly handling cases where any of those three columns might be NULL. Writing this kind of condition without explicit NULL checks (relying only on truthy/falsy assumptions) is a common source of leads silently falling through the cracks in real CRM-style applications.

A Mental Model for Debugging Complex Boolean Expressions

When I’m debugging a WHERE clause that’s returning unexpected results and it involves several AND/OR conditions, my go-to technique is to break the expression apart and test each piece independently as its own simple SELECT, verifying each sub-condition in isolation before recombining them.

-- Test each piece separately first
SELECT COUNT(*) FROM customers WHERE total_spend > 5000;
SELECT COUNT(*) FROM customers WHERE last_order_date < date('now', '-180 days');
SELECT COUNT(*) FROM customers WHERE flagged_at_risk = 1;

-- Then recombine once each piece behaves as expected
SELECT COUNT(*) FROM customers
WHERE (total_spend > 5000 AND last_order_date < date('now', '-180 days'))
   OR flagged_at_risk = 1;

This incremental approach has saved me an enormous amount of debugging time over the years — it’s much easier to spot a NULL-handling or precedence issue in a small, isolated condition than in one giant compound expression all at once.