Basics of Sub-Query – Sub-Query Essentials

Before we start with the above written topic let’s have a small picture of Sub-Query. A sub-query is a SQL statement which is implemented inside another SQL statement. I mean query i.e. nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query will be a Sub-Query.

Example: /* Query1. Simple Sub-Query. */
SELECT Prod_Description, Prod_Detail
FROM Product WHERE Prod_MSRP =
(SELECT Prod_MSRP FROM Product WHERE Prod_Description=
'Jumping Balls’)

In this example Select statement has been built using Sub-Query. A sub query can be used anywhere an expression is allowed.

Example: /* Query2. Simple Sub Query with expression */
SELECT POSH.SalesOrderID, POSH.OrderDate,
(SELECT MAX (POSOD.UnitPrice)
FROM PointOfSalesOrderDetail AS POSOD WHERE POSH.SalesOrderID =
POSOD.SalesOrderID) AS MaxUnitPrice
FROM PointOfSalesOrderHeader AS POSH

In this example a sub query is used as a column expression named MaxUnitPrice in a SELECT statement. At this point we can say that a sub query is also called an inner query or inner select, while the statement containing a sub query is also called an outer query or outer select.But in many T-SQL statements that include sub queries can be alternatively formulated as joins.

In T-SQL, usually there is no performance difference between a SQL statement that includes a sub query and a logically or we can say semantically equivalent version that does not. But in some cases where existence needs to be verified and must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases join approach will have better results.

Example: /* Query3. Simple SQL statement through joins (Alternate of Query1) */
SELECT PrdOne.Prod_Description, PrdOne.Prod_Detail
FROM Product AS PrdOne JOIN Product AS PrdTwo
ON (PrdOne.Prod_MSRP = PrdTwo.Prod_MSRP)
WHERE PrdTwo.Prod_Description = 'Jumping Balls’

Coming to basics, the database engine treats a sub-query as a virtual table for the execution of the query. A sub-query can be used as a table in a join statement, as a single value in a select statement, in the where clause of a SQL statement, in the having clause of a SQL statement, or incorporated in data manipulation statements.

Sub-query execution is dependent upon the nesting level of the query. The execution tree goes from inner-most queries to outer-most queries. The higher nested queries can access the results returned by the lower nested queries.
Finally we can say a sub query nested in the outer SELECT statement has the following components:
* A regular SELECT, FROM statement
* An optional WHERE, GROUP BY, HAVING clause.
-
Try above concept with SALARY, I mean Try to get second Max salary using Sub-Query. I think examples and exercise are the best ways to understand concepts. Feel free to put your queries and comments.
-
*Microsoft SQL Server 2008 Step by Step (Step by Step (Microsoft))