CODEWITHSUNDEEP
sqlsql-servert-sql
Dennis
Dennis

Reputation: 2616

Performance difference if I UNION first, then put WHERE on unioned result set?

In Microsoft SQL, is there any difference in performance between this:

SELECT columns FROM table1 WHERE cond
UNION
SELECT columns FROM table2 WHERE cond

and this:

SELECT columns FROM
(
SELECT columns FROM table1
UNION
SELECT columns FROM table2
) WHERE cond

?

Upvotes: 1

Views: 114

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332581

The former is preferrable because if index(es) exist on any column referenced in the WHERE clause (barring being wrapped in function calls), the index(es) can be used.

The latter is a derived table, so index use is out of the question.

Upvotes: 4

Related Questions