Hi, after check the execution plan, query are almost same in terms of query cost. Just to check whether any other different between below quey ? and which way is best way ? Select Field1, Field2 from TableA Where Field3 in ( Select Field3 from TableB Where Field4 = 'XXXX' and Field5 = 'YYY' and Field6 = 'ZZZZ') Select Field1, Field2 from TableA Inner Join ( Select Field3 from TableB Where Field4 = 'XXXX' and Field5 = 'YYY' and Field6 = 'ZZZZ') as Book On Book.Field3 = TableA.Field3 Select Field1, Field2 from TableA Where Field3 in ( Select Field3 from TableB Where TableB.Field4 = TableA.Field4 and Field5 = 'YYY' and Field6 = 'ZZZZ') and Field4 = 'XXXX'
You know the favourite saying "it all depends"...well in this case it is exactly that. Depending on the size of your tables, indexes, etc. In my opinion the first or last query is best from a coding perspective, but that is just because I don't like derived tables. Test all 3 queries and let the execution plans and execution times lead you in your decision.
Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. I would also encourage to experiment with using derived tables, views and temporary tables (if your time permits) to see what yields better performance results.