Join Elimination
Join elimination is the optimizer trick of replacing a query that contains a join with an equivalent query that doesn’t. Introduced in ECE459 L19. It may also rewrite a join into a nested subquery, on the theory that two small queries beat one big join.
Why should the DB rewrite the developer's SQL?
SQL specifies the what, not the how. If a faster equivalent exists, the server should take it, the same way a compiler silently rewrites code to a faster equivalent instead of scolding the programmer.
Constraints unlock this. Foreign keys and NOT NULL let the optimizer prove the join doesn’t change the result. That’s a second purpose of constraints: they enforce logical rules and enable optimization.
Library analogy
You’re asked to find all copies of “Harry Potter and the pthread House Elves”. If the library rule says only one copy of any book exists, you can stop searching as soon as you find it. Constraints let the optimizer apply exactly that kind of shortcut.
Complex queries make it harder to prove a join can be eliminated. More declared constraints mean more opportunities unlock.