Course contentsShow
← Software Engineering
Lesson 884 of 1,93327. Database Design in PracticeFree lesson

Query Rewriting for Performance

Learn techniques to rewrite queries using EXISTS vs IN, UNION vs OR, and subquery vs join transformations.

Query Rewriting for Performance

You'll learn how to transform slow queries into faster equivalents by choosing the right logical structures—EXISTS vs IN, UNION vs OR, and subqueries vs joins.

Why Query Rewriting Matters

Even when databases have proper indexes, how you phrase a query dramatically affects execution. Some query structures force the optimizer into suboptimal plans, while logically equivalent rewrites unlock better strategies. Query rewriting means expressing the same intent differently to help the database engine work smarter.

EXISTS vs IN

When checking if rows match values from another table:

  • IN loads all matching values into memory first, then checks each row
  • EXISTS stops searching as soon as it finds one match per row (short-circuits)

For large subquery results or when you only need to confirm presence, EXISTS often wins. Think of it like checking if any item in a warehouse matches vs loading the entire inventory list first.

UNION vs OR

When combining multiple filter conditions:

  • OR in a single WHERE clause can prevent index usage if columns differ
  • UNION (or UNION ALL if duplicates don't matter) lets the database run separate indexed lookups, then merge results

For queries filtering on unrelated columns, UNION ALL can leverage multiple indexes simultaneously.

Subquery vs Join Transformations

Correlated subqueries (running once per outer row) are often slower than joins, which process sets together. Modern optimizers sometimes rewrite these automatically, but explicitly converting subqueries to joins gives you control and clarity. However, subqueries can be clearer for "filtering" logic where you don't need the subquery's columns in results.

Key Takeaway:
Query rewriting—choosing EXISTS over IN, UNION over OR, or joins over subqueries—transforms logically identical queries into forms the optimizer can execute far more efficiently.