A subquery is an SQL statement that is used with another SQL statement. The subqueries are nested inside the WHERE or HAVING clause of the SELECT, INSERT, UPDATE or DELETE statements. The outer query is known as the parent query and the subquery is known as the inner query. The inner query is executed first and the result is returned to the outer query.

10.1 Using the IN and EXISTS keywords

User can specify different conditions on subqueries by using the following keywords:

1) IN

2) EXISTS

1) IN

If any sub query returns more than one value, user might need to match a column value with the value in the list returned by the inner query. The IN keyword is used to perform the task.

The syntax for using the IN keyword is as mentioned below:

Consider an example of STUDENT table as shown below:

The query for retrieving the records is as shown below:

The table after executing the query is as shown below:

2) EXISTS

User can also check the use of a sub query to check if the set of records exists. The EXISTS clause is used with the sub query. The TRUE or FALSE value is returned by the query.

The query containing the EXISTS keyword differs from other query. The keyword is not preceded by the column name, constraint or any expression. It contains an (*) asterisk in the SELECT clause of the inner query.

The syntax of the EXISTS keyword is as shown below:

Consider the example of the SUPPLIERS and ORDERS tables as shown below:

SUPPLIERS

ORDERS

The query to retrieve the records from the table is as shown below:

The output table from the query is as shown below:

10.2 Using modified comparison operators

In the subqueries, the comparison operators are used. They are used for creating condition that checks the value returned by the sub query. If the sub query returns more than one values, user needs to apply the operators to all the values returned by the sub query. The ALL and ANY keywords are used to modify the comparison operators.

The ALL keyword returns TRUE value if all the values that are retrieved by the sub query satisfy the comparison operator. If it returns FALSE value, if some values satisfy the comparison operator or if the sub query does not return any rows to the outer statement.

The ANY keyword returns TRUE, if any value retrieved from the sub query satisfy the comparison operator. It returns FALSE, if no values in the sub query satisfy the comparison operator or if the sub query does not return any row to the outer statement.

The following table shows the operators that can be used with the ALL and ANY keywords.

1) > ALL: It means that greater than the maximum value in the list.

2) > ANY: It means that greater than the minimum value in the list.

3) = ANY: It means any value in the list. It functions similar to the IN clause.

4) <> ANY: It means not equal to any value in the list.

5) <> ALL: It means not equal to all the values in the list. It functions similar to the NOT IN clause.

Consider the example of the CUSTOMER and AGENTS table.

CUSTOMER

AGENTS

The query to demonstrate the ANY keyword is as shown below:

The output for the query is as shown below:

10.3 Using Aggregate Functions

In the subqueires, the aggregate values of the inner query are generated using the aggregate functions.

Consider an example of the EMPLOYEE table.

The query through the use of the aggregate function is as shown below:

The output for the query is as shown below:

10.4 Using Nested Sub queries

A sub query can contain more than one subqueries. The subqueries are used when the condition of a query is dependent on the result of another query, that result is dependent on the result on another sub query.

Consider the EMPLOYEE table. The query for demonstrating the nested subqueries is as shown below:

The output for the query is as shown below:

10.5 Using Correlated Sub queries

The correlated sub query can be defined as a query that depends on the outer query for its result. In the sub query, the inner query is executed only once. The output of the inner query is provided to the outer query or the main query.

In the correlated sub query, the inner query is executed depending on the outer query.

Consider the example of the PRODUCT table.

The query for demonstrating the correlated query is as shown below:

The output for the query is as shown below:

10.6 Using APPLY operator

There is a condition when the user needs to combine the result of two queries such that each row of the first query, the second query is executed to show the rows returned. Consider an example of employee salary and the dividend provided on the salary.

There are two tables as employee and salary. The employee table contains the employee details and the salary table contains the pay information.

For displaying the records the user needs to retrieve the employee details and the salary information from the salary table. To combine the output of two query, the APPLY operator is used to display the data.

There are two types of APPLY operator supported by the SQL Server. They are as follows:

1) CROSS APPLY

2) OUTER APPLY

The syntax for the APPLY operator is as mentioned below:

Where,

left_table_expression represents the outer query result set.

right_table_expression represents the inner result set.

1) CROSS APPLY

The operator returns only those rows from the outer result set that matches the inner result set.

Consider the EMPLOYEE table as shown below:

Consider the SALARY table as shown below:

The user wants to retrieve the Employee_Name, Account_Number and Salary from the EMPLOYEE and SALARY table. The query by applying the CROSS APPLY is as shown below:

The output for the query is as shown below:

2) OUTER APPLY

The OUTER APPLY operator is used to return all rows from the outer result set even if the rows are no present in the inner result set. There is NULL value for the inner result set when the values do not match.

Consider the EMPLOYEE and SALARY table and apply the OUTER APPLY operator.