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 an ORDER-BY clause.
- A query can contain more than one sub-query.
Type of
Subqueries
- Single row subquery: Returns zero or one row.
- Multiple row subquery: Returns one or more rows.
- Multiple column subquery: Returns one or more columns.
- Correlated subqueries: Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
- Nested subqueries: Subqueries are placed within another subqueries.
Subqueries:
Guidelines
- There are some guidelines to consider when using subqueries:
- A subquery must be enclosed in parentheses.
- A subquery must be placed on the right side of the comparison operator.
- Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added in to a subquery. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be last clause.
- Use single-row operators with single-row subqueries.
- If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.
