Why are the reasons that WE use nested queries IN SQL server

Hello @Joy you can refers these sites

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb4c1901-6496-4485-9236-2fc5839d2a00/how-to-write-subqueries-in-a-storedprocedure-using-sql-server2000?forum=sqlgetstarted

 

http://dba.stackexchange.com/questions/68261/multiple-select-subqueries-in-an-insert-statement-in-a-stored-procedure

EXAMPLE 1

Get the employee numbers and enter dates of all employees with enter dates equal to the earliest date:

 

EXAMPLE 2

Get the employee numbers, last names, and jobs for all employees who entered their projects on October 15, 2007:

 

Types of Subquery

 

Nested Subquery

1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle

Non-Corelated Subquery

2) If a subquery is not dependent on the outer query it is called a non-correlated subquery

 

hi Joe,

 We use sub queries to improve the performance of the query.If we use joins or any other criteria to get results then sometimes it is very difficult to understand as well as low query performance,so at that occasion we use sub queries.

for example

 to count top max salary for an employee(here n is  employee'number who's salary to be found e.g. 3rd max salary)

 

Hope it may help you

read this link

https://technet.microsoft.com/en-us/library/ms189575%28v=sql.105%29.aspx 

 

it may help you 

Hello @Joe

 

Subquery mainly used for generate a result that will work as a input for outer Query.

Use of sub query is best when query are not more complex if query are more complex then sub query  should not be use in that case we should Use "CTE"

 

When dealing with sub-queries, it is often required that you need to select a part of the data from a sub query or even join data from a query with some other tables. In that case, either you have an option to name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and more complex and your query would look unmaintainable at any time. CTE allows you to define the subquery at once, name it using an alias and later call the same data using the alias just like what you do with a normal table. CTE is standard ANSI SQL standard.

 

SELECT * FROM  (         SELECT A.Address, E.Name, E.Age From Address A         Inner join Employee E on E.EID = A.EID) T WHERE T.Age > 50 ORDER BY T.NAME

The query looks really a mess. Even if I need to write something that wraps around the entire query, it would gradually become unreadable. CTE allows you to generate Tables beforehand and use it later when we actually bind the data into the output.

Rewriting the query using CTE expressions would look like:

With T(Address, Name, Age)  --Column names for Temporary table AS ( SELECT A.Address, E.Name, E.Age from Address A INNER JOIN EMP E ON E.EID = A.EID ) SELECT * FROM T  --SELECT or USE CTE temporary Table WHERE T.Age > 50 ORDER BY T.NAME

Yes as you can see, the second query is much more readable using CTE. You can specify as many query expressions as you want and the final query which will output the data to the external environment will eventually get reference to all of them.

So use subquery for small and simple query but for more complex query you should prefer "CTE"