Sql join resolution

Question

They say that we have the following SQL:

select * from ( select Id from table1 union all select id from table2 union all select id from table3 ) as X where Id in (1,2,3) 

Is the SQL optimizer smart enough to apply " where id in (1,2,3) " to each table before performing a join? Can we speculate about other database providers, such as Oracle, on this? Assumptions are always dangerous, but, I thought, I’ll ask anyway.

Answer 1

Try running this query in SQL Server Management Studio with the actual execution plan enabled. See if internal choices will look for an index on these keys.

I set up a test case with three tables that had an ID as a clustered primary key. The execution plan showed queries with a clustered index for the three selected identifiers.

However, if you really want internal queries to optimize their plans this way, you can put a WHERE clause for each subquery. For instance:

 select * from ( select Id from table1 where Id in (1,2,3) union all select id from table2 where Id in (1,2,3) union all select id from table3 where Id in (1,2,3) ) as X 

Also note that the above union all will potentially return duplicates if more than one of the tables has a matching Id . If this is a problem, you can change union all to just union like this.

 select * from ( select Id from table1 where Id in (1,2,3) union select id from table2 where Id in (1,2,3) union select id from table3 where Id in (1,2,3) ) as X 

I cannot guarantee this behavior in other database systems, but I would be surprised if their query optimizers did not do the same.

Answer 2

Many databases provide the ability to see a query plan to answer such questions. The execution plan will show the operations that the database server will perform to obtain the results of the query, the order of operations and some resource cost.

This site discusses an explanation of the plan you need to know if you are trying to optimize queries.

http://www.simple-talk.com/sql/performance/execution-plan-basics/