Question/Answer – Should I filter the data in Sub Queries??

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'
*/
use AdventureWorks2012;
select	isnull(p.FirstName+' ','')+isnull(p.MiddleName+' ','')+ p.LastName, p.BusinessEntityID
from	[Person].[Person] as p
where	p.LastName = 'singh'
go

select isnull(p.FirstName+' ','')+isnull(p.MiddleName+' ','')+ p.LastName, p.BusinessEntityID
from ( select * from [Person].[Person] as i where i.LastName = 'singh') as p
go

select isnull(p.FirstName+' ','')+isnull(p.MiddleName+' ','')+ p.LastName, p.BusinessEntityID
from ( select * from [Person].[Person] as i) as p
where p.LastName = 'singh'
go

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 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.