Using the OR Operator in SQL for Real Data Work

Most people learn OR early and then never really think about it again. That is a mistake. The operator itself is trivial, but the way it behaves under pressure in a real query is where things get interesting. I have spent years watching analysts write queries that look fine on paper and then choke when the data actually arrives. The OR clause is one of the most common culprits.

Here is the basic shape. You write a WHERE clause with multiple conditions and use OR between them. Something like WHERE status = 'active' OR status = 'pending'. That returns rows matching either condition. It works. It is simple. The problem starts when you add more conditions, join tables, or hit large datasets. When you are pulling data for analysis, you are rarely working with tidy, indexed, small tables. You are usually dealing with messy production databases that have bad indexes, skewed data distributions, and sometimes legacy schemas nobody wants to touch. I learned this the hard way a few years ago while building a churn prediction dataset for an e-commerce client. I needed to flag users who had either not logged in for 90 days OR had placed zero orders in the same window. My first query looked clean: SELECT user_id FROM users WHERE last_login_date DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) OR order_count_90d = 0

That query took 47 minutes to run against a table with about 12 million rows. Forty seven minutes. For a simple comparison. The table had an index on last_login_date but none on order_count_90d because it was a computed column updated via a nightly trigger. The database engine was doing a full table scan for the second condition and then merging results in a way that was brutally inefficient. I rewrote it using a UNION instead of OR, splitting it into two queries and combining the results. Runtime dropped to about 90 seconds. Not because the logic changed. Because the optimizer handled the two paths separately and could use the existing index properly. This is the thing most tutorials do not tell you. OR forces the query planner into a narrower set of execution strategies. In many cases, rewriting with UNION ALL gives the optimizer more flexibility and can be dramatically faster. This is not a universal rule. On small tables with good indexes on both columns, OR is fine and sometimes even preferred. But once you cross into multi-million row territory, the difference is noticeable and consistent.

How to Actually Use OR Well in Your Queries

Start by understanding how your database evaluates conditions. Standard SQL engines use short-circuit evaluation for OR, meaning they stop evaluating as soon as one condition is true. This is usually a good thing. It saves unnecessary comparisons. But it also means the order of your conditions matters. Put the cheaper, more selective condition first. If you have OR condition_a OR condition_b AND condition_c, the grouping is not what you might assume without parentheses. Always use explicit parentheses. WHERE (a = 1 OR b = 2) AND c = 3 is very different from WHERE a = 1 OR (b = 2 AND c = 3). I have seen both versions produce wildly different result sets when people assumed they were the same. Another thing people miss: NULL handling. OR behaves differently with NULL than most analysts expect. If you write WHERE col1 = 'x' OR col2 = 'y' and both col1 and col2 contain NULL for a given row, that row is excluded. NULL is not false. It is unknown. The whole expression evaluates to unknown and gets filtered out. If you need to include rows where either column might be NULL, you have to write it explicitly: WHERE col1 = 'x' OR col2 = 'y' OR col1 IS NULL OR col2 IS NULL. This is a common source of bugs in production reports where the output suddenly drops by 15 or 20 percent and nobody can figure out why. Index usage with OR is also more complicated than people realize. A single-column index helps when OR references the same column multiple times. WHERE id IN (1, 2, 3) is the same logical operation as WHERE id = 1 OR id = 2 OR id = 3, but the optimizer handles the IN version more efficiently in most engines. When OR spans different columns, you typically get index intersection or a full scan, depending on the engine. PostgreSQL and MySQL both support index intersection for AND queries but handle OR queries differently. MySQL often falls back to a full scan. PostgreSQL will attempt a bitmap heap scan. Both are worse than what you get with a well-placed covering index.

Get the Full Details

SQL For Data Analysis Cheat Sheet A4 | PDF
SQL For Data Analysis Cheat Sheet A4 | PDF

If you are doing repeated analysis work with OR-heavy filters, consider adding a generated column or a materialized view. I maintain a analytics dataset where I pre-compute several common filter combinations as materialized columns. Yes, it adds write overhead. Yes, it requires maintenance. But read queries that used to take 30 seconds now take under 200 milliseconds. The trade-off is worth it when you are running the same analytical query dozens of times a day.

When OR Is the Wrong Tool

Sometimes the problem is not your OR clause. Sometimes the problem is that you are asking the database to do aggregation work it is not built for. If you are using OR to combine rows from different categories and then aggregating across the result, a CASE statement inside an aggregate function might be cleaner and faster. Instead of filtering with OR and then grouping, you can conditionally include or exclude values within the aggregation itself. This reduces the data volume before the group by rather than after. There are also cases where OR simply cannot save you. If you are joining three or more large tables and every condition in your WHERE clause uses OR across different tables, you are going to have a bad time regardless of how you write it. Partition pruning, query restructuring, and sometimes moving the logic into an ETL pipeline become necessary. I once had a query with five OR conditions across four joined tables that the optimizer refused to parallelize. I ended up breaking it into four separate CTEs, each pulling a slice of the data, and then UNION ALLing them together at the end. The query went from a 12-minute timeout to about 4 minutes. Four minutes is still too long, but it was the best I could do without changing the underlying schema or adding new indexes. The bottom line is that OR in SQL is straightforward in theory and unreliable in practice if you do not pay attention to how your specific database engine handles it. Learn your optimizer. Test your queries against realistic data volumes. Watch the execution plans. The difference between a query that runs overnight and one that runs in minutes is usually a single structural change that has nothing to do with the logic itself.