What is sub-query? Explain properties of sub-query.
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.
A subquery is a SELECT statement that is nested within another T-SQL statement. A sub query SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a sub query SELECT statement can standalone and is not depended on the statement in which it is nested. A sub query SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a
T-SQL statement. A Sub query can also be used as a parameter to a function call. Basically a sub query can be used anywhere an expression can be used.
Properties of Sub-Query
A sub query must be enclosed in the parenthesis.
A sub query must be put in the right hand of the comparison operator, and
A sub query cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.
What are types of sub-queries?
Single-row sub query, where the sub query returns only one row.
Multiple-row sub query, where the subquery returns multiple rows,.and
Multiple column sub query, where the sub query returns multiple columns.