Transact SQL supports querying through sub queries. In general sometimes there is a need to query a temporary result set and with respect to that result set you need to further do the computation.
Sub Queries can either be a simple self-contained queries which can be queried independently in respect to outer query or it can be a co related query where in general there is always a co-relation to the outer table column. Based on this type we can have results set in either in a scalar value(single column value), or multi valued( single column with n no of rows), or a table valued.
Self contained queries
Self contained queries are easy to work on as they can be run independently with respect to the outer query. Just highlight the inner query and you can get the desired result. It is also therefore easy to understand the code in comparison to correlated sub queries.
Sub queries that results to single or scalar value is used with the where clause. For example we can find the products with the minimum unit price by using sub query as follows.
The inner query will fetch the minimum unit price from the products table. This minimum price then will be further initialized to the outer query where in the outer query is asking to show all the details of product with minimum unit price.
As said above, we can also just highlight the inner query to find out just the minimum price of products and there will be no error.
If the returns more than one value then it will result to error at run time, since you are using just a where clause which only filters to scalar or single result set. If the result set is an empty then it is converted to NULL.
A sub query can also return multiple values from a single column. Such a query can be used when more than one value is expected from a single column. For such queries can use an IN predicate.
In the above example, the inner query filters the supplierid which are from the city London, and then these supplierids are initialized to outer query so as to get the products details of those suppliers.
You can also negate the result, like if you want the details of all the products except for those suppliers who are not from the city London. This can be achieved by simply using NOT IN predicate after the where filter.
Correlated Sub Query
A correlated sub query is a bit more complex in comparison simple sub query. Though not that difficult either. A correlated query is a query which is interdependent to the outer query with respect to the columns being used as comparison between outer and inner query. A simple nested sub query usually processed only once and that result is passed to outer query which also executes once.
To understand and have a clear picture take is as an example say you want to find the minimum price of a product per category. In this type of situation you can use a correlated query where in you can equate the categoryID of inner query with the categoryID of outer query. Which will then find the minimum price among those same categories.
The flow of query works in 3 parts.
- The outer query obtains the result and passes to inner query.
- The inner query executes based on the passed value.
- The inner query then passes the result back to outer query which is the final result for processing.
Lets see the below example and understand.
The query uses two instance of a same one table so as to distinguish between themselves. The query uses a correlation with a predicate p.categoryid=pp.categoryid to filter minimum value among a particular id. So if the category id is 1 from outer query then it will find the minimum unit price for that category by comparing in inner query. Once compared it will pass the result back to outer query and this goes on till all the categoryid are not compared.
Correlated sub query can also be used with EXISTS predicate. For example see the below example.
The EXISTS predicate does not return any result set, rather it will return either a true or false result once the required condition is been satisfied. Over here the condition is this that it will return those customers who has placed the order on date “2007-02-12”. Also due to the fact that it only returns a true or false, the SQL Server Query Optimizer ignores the inner Select list and therefore optimizing inner query won’t affect any optimization.
With EXISTS predicate you can negate the result set. Say you want to know the customers who din’t placed orders on “2007-02-12”. In that just use the NOT EXISTS and you will get the required result.
Correlated queries can also be used to provide a different type of result set. Like suppose you want to have an aggregate on one field but you don’t want this to happen to other fields. For example you want to know the details of all the customers who have ordered recently. Then you can use the query as provided below