SQL server interview question: -What are Sub-Queries, co-related queries and difference between them?

Note :- This is a favorite interview question which keeps coming up , so I have put a detailed explanation of the same.

Sub query a.k.anested queries is a Query inside Query. Many times you would like to have series of SQL chained where output of one query is sent as input to the other query for filtering and manipulation.

Courtesy: -http://www.yankodesign.com

For example you can see in the figure below we have two tables one table has the employee salary and the second table has phone numbers. Let’s say we want to find phone numbers of employees whose salaries are greater than 150.

We can achieve this by using subquery. One query ( i.e. inner query ) will get all employee whose salaries are greater than 150. This output will be fed to another query who will pull out phone numbers accordingly.

In simple words we will have two queries one inner query and the other an outer query. The output of inner query is fed to the input of outer query and then the final output is displayed.

The Sub query will look something as shown below. First we fetch from the EmpSal table and get salary values which are more than 150. The results of this query is sent to outer query and finally the output is displayed.

Now let’s say if we want to find employees who have the second highest salary from the salary table , so how will you go about it.

This can be achieved by using co-related queries. The query would look something as shown below. In this case we need to evaluate each record to see the position of each one of them and then fetch second highest accordingly.

In other words first the outer query will send record to the inner query, who will then evaluate the same until the conditions are met.

In case you are still confused you can go through following youtube video where I put a detailed explanation about the same.

Putting in simple words in co-related queries data moves back and forth for each record while in subquery the data moves unidirectional from the inner query to the outer query.

So below is a simple summary of what you can speak in the interview.

See for more stuffs on SQL Server Interview question

Regards,

Click to view more from author's on SQL Server Interview question