Recently a good friend of mine asked me question whether is there any performance benefit in filtering the data out in subqueries, and then joining them.
Well, answer is NO. If you keep the same tables with exact same predicate logic, it does not matter whether you convert a simple query into sub queries. The thing which matters are the columns(data) selected in outermost select clause, and the predicates used to filter out the data. SQL Server re-factors codes to make it simple as long as the output is same. Let’s conclude this thesis by queries.
Below are the 3 queries doing exactly same thing, but constructed in difference ways.
/* Queries to Take out Persons with LastName 'Singh'
Now, below are the execution plans for above queries:-
As we all can see, the actual query plan for all the 3 queries are exactly same. So, we can conclude that if the filters and output columns are kept same, logically it does not matter if we place the tables/filters in sub-query or not.
Thanks Friends. If you like the blog post, do like and share. Happy Coding 🙂
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.