SQL UNION Optimization

I have 4 tables named A1, A2, B1, B2.

To fulfill a requirement, I have two ways to write SQL.

The first one is:

(A1 UNION ALL A2) A JOIN (B1 UNION ALL B2) B ON A.id = B.a_id  

And the second one is:

(A1 JOIN B1 on A1.id = B1.a_id) UNION ALL (A2 JOIN B2 on A2.id = B2.a_id)  

After UNION and JOIN, I also need filter the query by multiple criteria.

I did try both approaches and realized both have the same execution time and query plan in some situations. But I'm not sure they always have the same performance. So my question is when the first one is the better choice, and when the second one is better

https://stackoverflow.com/questions/67239853/sql-union-optimization April 24, 2021 at 02:17PM