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.
WHERE name = (SELECT name
WHERE repid = 10)
• 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.
WHERE repid < ANY (SELECT repid
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.
WHERE repid < ALL (SELECT repid
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.
"=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.
WHERE repid = (SELECT repid
WHERE custid = 3)
OR creditlimit > (SELECT creditlimit
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.
WHERE ordid = (SELECT ordid
WHERE custid = (SELECT custid
WHERE total = 7000));
A sub query can retrieve information from more than one table.
Consider the following example.
WHERE custid IN (SELECT custid
FROM order_info o,
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.
FROM order_info o
WHERE shipdate < ANY (SELECT orderdate
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.
• 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.