Use A Union Or A Join - What Is Faster

Question

I just wonder if you had a table and you unioned it would it be more efficent then using a join??

I do know that the join creates more columns but this is more theoretical - Will the union need to do a nested loop scan of the other table like a join would have to?

Answer 1

A single SELECT will use no more than one index per table. A UNION will use no more than one index per SELECT in the union.

The UNION will make better use of indexes which could result in a faster query.

Answer 2

JOIN and UNION have two different purposes. JOIN is used to add additional tables to a query for the purpose of adding selection criteria and possibly additional columns. UNION is used to combine the results of two distinct queries with the same columns. Asking which is more efficient is like asking which of "a loaf of bread" and "the blowing wind" is more "orange".

Answer 3

Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.

The Join will go through each row of both tables, finding matches in the other table therefore needing a lot more processing due to searching for matching rows for each and every row.

EDIT: By Union I mean Union All as it seemed adequate for what you were trying to achieve. Although a normal Union is generally faster then Join.

Answer 4

sorry to break your party, but a well written join will be faster than a union.

• it uses more lightweight statistics collection model (based on cardinality, rather than random dives)
• query will get parsed only once (no need for multiple subselect evaluation)
• resultset will not be materialized in a temptable (it gets even for UNION ALL)