SQL Noob (10/7/2009)
is there any performance difference doing a union all compared to a join when you need to get all the rows from two or more tables? i have a database where i do union all on some data in 20 tables or so when running a report and it seems to take a long time
I'm not certain I understand your question.
A UNION ALL combines the data from two similar sets into a single set with all members of each set.
A JOIN matches the data in one set with the data in another set.
Based on my current knowledge of SQL, I believe it might be theoretically possible to replicate a UNION ALL in a JOIN (although even in theory I think it would be dependent on your table structure), but it would be atypical and in all likelihood it would take longer to run than a straight "union all".
I have not tested this theory.
I'll admit, even as I attempt to consider an alternative that involves a JOIN, I still envision needing a UNION ALL in the query and a requirement for each record to have a unique id across all tables.