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

Question

I mean I often use nested queries to solve the queries in SQL SERVER but I didn't definitely know about the reason of using them and sometimes it get so complicated that it is difficult to find the path between entities.

Pankaj Kumar Choudhary · Answer

Hello @Joe check these sites http://www.codeproject.com/Articles/126898/Sql-Server-How-To-Write-a-Stored-Procedure-in-SQL http://www.aspsnippets.com/Red/Articles/How-to-write-a-stored-procedure-in-SQL-Server-Management-Studio.aspx Stored Procedures Best Practices - CodeProject Stored Procedure Generator For SQL Server - CodeProject Overview of SQL SQL Server – How to write stored procedures with output ... Server Stored Procedure - CodeProject Code First Stored Procedures - CodeProject

Joe Wilson · Answer

Thank you for your suggestions

Joe Wilson · Answer

Thank you for your answer but they were just examples please suggest a few websites to learn store procedures

Pankaj Kumar Choudhary · Answer

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

Joe Wilson · Answer

Thank you for your explanations and examples  by the way I have problem related to using store procedures in SQL SERVER could you please suggest few websites related to this? and I need a few examples according to store procedures.

Pankaj Kumar Choudhary · Answer

Subquery or Inner query or Nested query is a query in a query. SQL subquery is usually added in the WHERE Clause of the SQL statement. Most of the time a subquery is used when you know how to search for a value using a SELECT statement but do not know the exact value in the database. Subqueries are an alternate way of returning data from multiple tables. Subqueries can be used with the following SQL statements along with the comparision operators like =  >= 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: SELECT p.product_name FROM product p WHERE p.product_id = (SELECT o.product_id FROM order_items o WHERE o.product_id = p.product_id); 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

Harminder Singh · Answer

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 performanceso 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) without sub query SELECT distinct Salary as max_salary FROM Employee Emp1 WHERE (n-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary) with sub query SELECT *FROM Employee select top 1 salary from employee where Salary in (select distinct top 3 Salary from Employee order by Salary asc) order by Salary desc Hope it may help you

Joe Wilson · Answer

Well your explanation was very good but I couldn't see the examples completely.please resend the examples and add a few examples to the post too.

Joe Wilson · Answer

Thank you very much for your answer that was so useful.

Munesh Sharma · Answer

http://beginner-sql-tutorial.com/sql-subquery.htm

Nanhe Siddique · Answer

read this link https://technet.microsoft.com/en-us/library/ms189575%28v=sql.105%29.aspx it may help you

Pankaj Kumar Choudhary · Answer

Hello @Joe A subquery is a query that is nested inside a SELECT INSERT UPDATE or DELETE statement or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement. 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