Sub query/Nested Query/Inner Query in SQL

            Sub Query/Nested Query/Inner Query in SQL

Sub query or Inner query or Nested query is a query in a query. A sub query is usually added in the WHERE Clause of the SQL statement.

A Subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow:

• Subqueries must be enclosed within parentheses.

• A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.

• An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.

• Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.

• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.

• A subquery cannot be immediately enclosed in a set function.

• The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

See also:  Difference between CTE and View - SQL, T- SQL, SSIS, SSRS Interview Questions and Answer