Union vs Or Performance

Choose between logical OR and UNION operators based on index availability.

In this lesson, you will learn how to choose between the OR operator and the UNION operator to ensure your database queries run at peak performance.

The Problem with OR

When you use a logical OR in a WHERE clause, you are telling the database engine to check every single row to see if it meets either of your conditions. If you are searching for rows where status = 'active' OR category = 'urgent', the database engine often performs a Full Table Scan. Even if you have indexes on both columns, the query optimizer might struggle to use them simultaneously, forcing it to read the entire table from disk, which is slow.

The Efficiency of UNION

A UNION query breaks the problem into two distinct parts. Instead of asking for everything at once, you execute two separate, simpler queries—one for each condition—and then combine the results:

SELECT id, name FROM tasks WHERE status = 'active'
UNION
SELECT id, name FROM tasks WHERE category = 'urgent';

Because each part of the UNION is a simple WHERE clause, the database can easily perform an Index Seek for both conditions. It jumps directly to the relevant rows in the index rather than scanning the whole table.

Making the Right Choice

So, when should you pick one over the other?

Use OR when: Your table is small, or you are filtering on a single column (e.g., id = 1 OR id = 2). In these cases, the overhead of running two separate queries with UNION is greater than the cost of a simple scan.
Use UNION when: You are filtering on different columns that are indexed, and your table is large. By splitting the logic, you allow the database to leverage the power of its indexes effectively, resulting in significantly faster retrieval times for high-volume data.

Always remember that while UNION is generally faster for complex filtering on large datasets, it does come with the minor cost of running two separate execution plans. Test both approaches if you are ever in doubt!

Key Takeaway: Use UNION to split complex filtering into multiple index-friendly operations, effectively avoiding the "Full Table Scan" performance trap caused by the OR operator.