SQL Server interview questions :- What are Sub Query and Co-related Queries ?
Articles,  Blog

SQL Server interview questions :- What are Sub Query and Co-related Queries ?


In this video will talk about what is a subquery what are co-related queries and what’s a difference between subquery and co related queries so first let start the subquery subquery also termed as nested queries subquery is nothing but it’s a query inside a query many times you know you would like to you have series of SQL chained you know where output of one query is sent as the input to the other query for filtering and manipulation for example you can see in the figure you know we have two tables one table has the employee salary and the second table has phone numbers now let say that we want to find phone numbers of employees you know who have salaries you know which are greater than 150 now this can be achieved by using subquery so what we can do is we have two queries one is the inner query which will go and fetch you know records you know which are greater than 150 and the output of this will be fielded to outer query who will go fetch the phone numbers so first thing is first will go and select so let’s go and query the EmpSal table so will say okay select id from EmpSal where salary is greater than 150 so this will be our inner query now the output of this inner query will be fetch to a query which is outer query which will actually go and extract phone numbers so what I will do is I will say okay this is my inner query okay and the output of this inner query will now go will be now sent to outer query so this a outer query selects * from Emp details details so expand this bit where Id in okay just let me go and indent this so that everybody see properly also there is a red sign here so this is actually idfk okay so id fk right so now you can see that you know what will happen is first the inner query will go will evaluate himself he will extracts records you know whose salary is greater than 150 those id is will then we freed to this outer query who will say okay ids you know which are been supplied by this inner query you know I will go and extract Emp details out of it okay so let me just go and comment this so you can see now this is the outery query and this is a inner query so if I go and execute this you can see now he showing me phone numbers of phone numbers as well as address you know of employees whose salaries are greater than 150 so if you just go and recheck this if this proper or not if I go and execute this you can see that we have two employees here who salaries are greater than 150 one is xyz and the other one is sreeni so three and four so if I go and fire this SQL again you can see that he has selected those third and the fourth record and he has taken the phone numbers and address out of it so this is what a subqueries subquery is nothing but a it’s a query inside query we have a inner query so first the inner query gets evaluated the data from the inner queries then supplyied to the outer query and the complete data is then display or the complete output is then displayed this was about subquery now there is one more thing which we discuss which we said will discuss in this video is co-related queries so let me just go and delete all this now let’s take a situation where you want to go and find the second highest from this table EmpSal so how will go about doing that now this can be achieved by using co related query so what I will do is first let me go and write down the co-related query here and then I will explain how exactly it operate so I want to go and paste this co-related query here and let me first explain me this SQL and then we will go about execute in this now the first thing which will notices is that there is a inner query and that is a outer query so you know you can think about that you know co-related queries are a type of variation of subquery or nested queries so one similarity between both them you know both have inner query and both have a outer query but you know there is a big difference in the way the data is pass between inner query and outer query let me explain that so you can see here that you know your inner query is also referencing the outer query table so you can see that I have the EmpSal table right I have defined two aliases here you can see I am saying EmpSal e1 which is reference in the outer query and then we have Emp Sal e2 which is again reference in the inner query and you can see here that I am saying that e2 .salary should be greater that e1. salary in other words you know my inner query is reference in the outer query if you remember in subquery right they will no references so the you can think that in subquery right the inner query was completely independent and standalone but here you can see that there is a relation between the inner query and outer query actually the inner queries the referencing the outer query table that’s why the name is co-related queries so here’s how thing will work so what happens here is the outer query record is pass to the inner query the inner query you know evaluates that record and passes the data again back to the outer query so here’s how it works so first thing it goes record by record so first shiv will be taken okay from the outer query the shiv record will be pass to the inner query in the inner query we say that how many salaries are greater than and equal to shiv that is hundred so we have one two three okay and also equal to hundred so again you know we have even shiv included in that so four okay so we say okay we want to find this second highest here now we are trying to find the second highest okay second highest so when the shiv record is pass to the inner query he will say okay it will actually give out the count of four and this is not equal to two okay so that record will be readdicted then it will take the second one raju now raju 150 again pass to the inner query the output will be one two three again it is not the second highest after that xyz will be taken again it will be pass to the inner query now will say okay how many records are greater than 200 or equal to 200 so we have one 300 and he himself so that is equal to two and this is the second highest so in other words every record from the outer query is pass to the inner query the inner query then again evaluates it and then again it is pass to the outer query so if I go and executes this now you you can see that he displaying me xyz which is actually the second highest salary so now let’s summarise you know what exactly is a subquery and what exactly is a co-related query and also let’s try to point out the important differences between them in subquery right the inner query is independent it is self content and it’s gets evaluated first and then the complete record is passed you know to the outer query and the data is displayed after that while in co-related query is right the inner query he needs values from the outer query and then you know these results are pass back to the outer query so you can think about the subquery it’s more of unidirectional thing where the data just most the inner query to the outer query while in co related queries right the data moves to and fro and finally the final output displayed after it matches the where clause so I hope that you enjoy this video in this video we will trying understand what exactly is a suquery what exactly is co-related query and what are the importance differences between them thank you so much now whatever video you have seen right is just gleams of what we have done so in case you are interested in our video package you can go to our site that is www.questpon.com you can call on number and you can ask the complete DVD package what we have so in this DVD package what we have done is basically we have covered almost everything what a .NET developer wants so right from basics of ASP .NET objected programming SQL Server to new technologies like WCF Silverlight Linq Azure entity framework we also have UML architecture estimation project management that it is complete invoicing package invoicing project end to end which is covered so that you can better feel of how to actually create projects in a systematic manner we have cover server products you know both for sharepoint 2007 as well as 2010 we have lot of best practices videos on SQL Server etc so this complete package you know you can get from www questpond.com if you are interested and you can call on this number and you can ask for the rates it’s very decent rate what we have in the same way you know as compare to the videos we also have one more products with us that is our interview question books so we have different kinds of interview questions books you know right from .NET interview questions to SQL Servers interview questions sharepoint interview questions BizTalk interview questions etc so in case you are interested in the books part you can call on these numbers as per your location so you can see this numbers on bored at this moment so I hope that you keep enjoying the videos you keep seeing our site and I hope that you gain more knowledge thank you very much

58 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *