SQL Server - Sub Queries
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.
Sub Queries:-
Usage of Sub Queries:-
Nesting of Queries, one within the other id termed as a sub query. A statement containing sub query is called a parent statement. Sub Queries are used to retrieve data from tables which depend on the value in the table itself. The following example is illustrative of this.
Example 1:-
SELECT repid,
address,
name
FROM customer
WHERE name = (SELECT name
FROM cutomer
WHERE repid = 10)
Note:
• Always enclose sub query in parenthesis.
• Sub query will be evaluated first followed by the main query.
Sub queries that return several values:-
The above example used to a sub query which returned a single value. Sub queries can also return more than one value. In such cases we should include operators like ANY, ALL, IN or NOT IN between the comparison operator and the sub query.
The following example illustrates the usage of the any operator.
Example 2:-
SELECT name,
address,
city
FROM customer
WHERE repid < ANY (SELECT repid
FROM cutomer
WHERE creditlimit = 10)
In the above example the sub query will display that area which has creditlimit equal to 45345. The main query will display details about customers if area is lesser than any of the values returned by the sub query.
Example 3:-
SELECT name,
address,
city
FROM customer
WHERE repid < ALL (SELECT repid
FROM cutomer
WHERE creditlimit = 10)
In the above example the sub query will display area which has creditlimit equal to 45345.the main query will display details about customers only if area is lesser than all the values returned by the sub query.
Note
"=ANY" is equivalent to IN and "! =ALL" is equivalent to NOT IN.
Multiple Sub queries:-
SQL places no limit on the number of queries included in a WHRER Clause
Consider the following example which illustrates multiple queries.
Example 4:-
SELECT name,
repid
FROM customer
WHERE repid = (SELECT repid
FROM cutomer
WHERE custid = 3)
OR creditlimit > (SELECT creditlimit
FROM cutomer
WHERE custid = 2)
The above example will select the columns name, repid from customer provided one of the above sub queries is true.
A sub query itself can contain a sub query. The following example is illustrative of this statement.
Example 5:-
SELECT *
FROM order_info
WHERE ordid = (SELECT ordid
FROM order_info
WHERE custid = (SELECT custid
FROM order_info
WHERE total = 7000));
A sub query can retrieve information from more than one table.
Consider the following example.
Example 6:-
SELECT name,
city,
address
FROM customer
WHERE custid IN (SELECT custid
FROM order_info o,
item I
WHERE o.ordid = i. ordid)
In the above example the sub query refers to two tables, order_info and item. It will display rows from customers table for the cusid returned by the sub query.
Correlated Sub query:-
A Sub query is evaluated once for the entire parent statement whereas a correlated sub query is evaluated once per row processed by the parent statement. Consider the following example which returns rows for total which are greater than AVG (Total) with reference to ordid.
Example 7:-
SELECT ordid,
orderdate,
shipdate
FROM order_info o
WHERE shipdate < ANY (SELECT orderdate
FROM order_info
WHERE o. ordid = ordid);
The behaviour of a correlated sub query is evaluated as follows
• Ordid of the row is first determined.
• Ordid is then used to evaluate parent query.
• If that row's shipdate is greater than any of the orderdates then the row is returned.
• The sub query is evaluated once for each row of the order_info table until all the rows of the table have been tested.
Note:-
• If the sub query is selected from the same table as the main query, the main query must define as alias for the table name and the sub query must have use the alias to refer to the column's value in the main query.
• Sub query can also be include in delete, update and insert commands
Nice explanation but using Sub Queries there are some disadvantages like performance effect to overcome that there is an option called as Joins using Joins we can fetch data with in time limit.