Back To Blog

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

By Shiv Prasad Koirala in SQL
Jul 2nd, 2012
21820
0

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.

sql

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.

sql

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.

sql

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.

sql

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.

sql

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.

sql

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.

sql

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

Subquery	Co-related
The inner query is independent / self-contained and gets evaluated first and then passes the results to the outer query.	The inner query needs the values from the outer query and passes results to the outer query.

sql