Menu
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?
460
Using the JOIN clause, we combine the attributes of two given relations and, as a result, form tuples. Whereas we use the UNION clause when we want to combine the results obtained from two queries. They both combine data differently.
If you dont include the items of the left joined table, in the select statement, the left join will be faster than the same query with inner join. If you do include the left joined table in the select statement, the inner join with the same query was equal or faster than the left join.
The reason is that using OR in a query will often cause the Query Optimizer to abandon use of index seeks and revert to scans. If you look at the execution plans for your two queries, you'll most likely see scans where you are using the OR and seeks where you are using the UNION .
Which SQL join is the fastest? You may be interested to know which is faster – the LEFT JOIN or INNER JOIN. Well, in general INNER JOIN will be faster because it only returns the rows matched in all joined tables based on the joined column.
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.
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.
I don't agree with him. Technically speaking no matter how good your join is, a "JOIN" is still more expensive than a pure concatenation. I made a blog post to prove it at my blog codePERF[dot]net. Practically speaking they serve 2 completely different purposes and it is more important to ensure your indexing is right and using the right tool for the job.
Technically, I think it can be summed using the following 2 execution plans taken from my blog post:
UNION ALL Execution Plan
JOIN Execution Plan
Practically speaking the difference on a clustered index lookup is negligible:
190
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
