Noncorrelated subqueries allow each row from the containing SQL statement to be compared to a set of values. Divide noncorrelated subqueries into the following three categories, depending on the number of rows and columns returned in their result set:
Single-row, single-column subqueries
Multiple-row, single-column subqueries
Depending on the category, different sets of operators may be employed by the containing SQL statement to interact with the subquery.
Single-Row, Single-Column Subqueries
A subquery that returns a single row with a single column is treated like a scalar by the containing statement; not surprisingly, these types of subqueries are known as scalar subqueries. The subquery may appear on either side of a condition, and the usual comparison operators (=, <, >, !=, <=, >=) are employed. The following query illustrates the utility of single-row, single-column subqueries by finding all employees earning an above-average salary. The subquery returns the average salary, and the containing query then returns all employees who earn more than that amount.
WHERE salary > (SELECT AVG(salary)
FROM EMPLOYEE);LNAME -------------------- Brown Smith Blake Isaacs Jacobs King Fox Anderson Nichols Iverson Peters Russell
As this query demonstrates, it can be perfectly reasonable for a subquery to reference the same tables as the containing query. In fact, subqueries are frequently used to isolate ...