What are Subquery and Co-related Queries in SQL Server ? | SQL Server Interview Questions & Answers

in this video we'll talk about what is a sub-query what are correlated queries and what's the difference between sub query and Co related queries so first let's start this sub query sub queries also termed as nested queries sub query is nothing but it is a query inside a query many times you know you would like to have series of SQL chained in a 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's 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 sub query so what we can do is we have two queries one is the inner query which will go and fetch inner records you know which are greater than 150 and the output of this will be will be fitted to outer query who will go and fetch the phone numbers so first thing is first we'll go and select so let's go and query the EMP cell table so we'll say okay select ID from EMP Sal where a salary is greater than 150 so this will be our inner query now the output of this inner query will be fed to a query which is the 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 is our outer query select star from EMP details details square expand this bit we're ID in okay just let me go and indent this so that everybody can see it properly also there's a red sign here so this is actually idfk okay so ID FK right so I can see that you know what will happen is first the inner query will go we'll evaluate himself he will extract records you know whose salary is greater than 150 those IDs will then be fed to this outer query who will say ok IDs you know which have been supplied by this inner query you know I'll go and extract the EMP details out of it ok so let me just go and comment this so you can see now this is the outer query and this is the inner query so if I go and execute this you can see now he's showing me phone numbers of phone numbers as well as address you know of employees whose salaries are greater than 150 so if you if just to go and recheck this if this is proper or not if I go and execute this you can see that we have two employees here whose salaries are greater than 150 one is X Y Z and other one is shalini so three and four so if I go and fire this SQL again we can see that he has selected the third and the fourth record and he has taken the phone numbers and address out of it so this is what a sub queries a sub query is nothing but it is a query inside query so we have an inner query so first the inner query gets evaluated the data from the inner query is then supplied to the outer query and the complete data is then displayed all the complete output is then displayed so this was about sub query now there is one more thing which we discussed which we said we'll 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 EMP Sal so how we will go about doing that now this can be achieved by using Co related queries so what I will do is first let me go and write down the correlated query here and then I will explain how exactly it operates so I am going to go and paste this correlated query here and let me first explain to this SQL and then we will go about excluding this now the first thing which you will notice is that there is a inner query and then there is a outer query so you know you can think about that you know Co related queries are a type of variation of sub query or nested queries so one similarity between both of them is you know that both have an inner query and both have our outer query but you know there is a big difference in the way the data is passed between inner query and outer query so 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 EMP cell table right I have defined to Alice's here you can see I am saying EMP Sally 1 which is a reference in the outer query and then we have EMP Sal e 2 which is again referenced in the inner query and you can see here that I am saying that e 2 dot salary should be greater than e 1 dot salary in other words you know my inner query is referencing the outer query if you remember in sub query right you know there were no references so though you can think about that in in in in sub query write the inner query was completely independent and standalone but here you can see that you know there is a relation between the inner query and outer query actually the inner query is referencing the outer query table that's why the name is correlated queries so here's how this thing will work so what happens here is the outer query record is passed 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 ship will be taken okay and from the outer query the ship record will be passed to the inner query in the inner query we say that okay how many salaries are greater than an equal to ship that is 100 so we have 1 2 3 ok and also equal 200 so again you know we have even ship included in that so 4 ok so we say ok we want to find the second highest here now we are trying to find the second highest ok second highest so when the ship record is passed to the inner query he will say ok it will actually give out a count of 4 and this is not equal to two okay so that record will be eradicated then it will take the second one Raju now Raju 150 will be again passed 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 through the inner query now we 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 passed to the inner query the inner query then again evaluates it and then again it is passed to the outer query so if I go and execute this now you can you can see that he is displaying X Y Z which is actually the second highest salary so now let us summarize you know what exactly is a sub query and what exactly is a correlated query and also let us try to point out the important differences between them in sub query write the inner query is independent it is self-contained and it 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 correlated queries right the inner query reads the values from the outer query and then you know these results are passed back to the outer query so you can think about in sub query on it's more of a unidirectional thing you know where the data just moves from the inner query to the outer query while in correlated queries right the data moves to and fro and then finally the final output is displayed you know after it matches the where clause so I hope that you enjoyed this video in this video we were trying to understand what exactly is a sub query what exactly is a correlated query and what are the important differences between them thank you so much now whatever video you have seen right is just a glimpse of what we have done so in case you are interested in our video package we can go to our site that is wqf on comm you can call on this number and you can ask the complete DVD package what we have so this DVD package what we are done is basically we have covered almost everything what a.net developer wants so right from basics of asp.net object of programming SQL server to new technologies like WCF silverlight linq azure entity framework we also have uml architecture estimation project management there is a complete invoicing project end-to-end which is covered so that you can get a better feel of how to actually create projects in a systematic manner we have covered server products you know both for sharepoint 2007 as well as for 2010 we have lot of best practices video on SQL server etc so this complete package you know you can get from w crisp on comm if you're interested and you can call on this number and you can ask for the rates it's it's a very decent rate what we have on the same in the same way you know as compared to the videos we also have one more product with us that is our interview question books so we have different kinds of interview question books you know right from from dotnet interview questions to SQL server interview questions sharepoint interview questions biztalk interview questions etc so in case you are interested in the books part you can can call on these numbers as per your location so you can see these numbers on the board 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

Channel: Questpond

Views: 357,974

Rating: 4.8432469 out of 5

Keywords: SQL server interview questions, Subquery, corelated, queries, SQL, Learn Sql Server, Step By Step Sql Server

Id: 00Vxnod-6iE

Length: 9min 38sec (578 seconds)

Published: Mon Jul 02 2012