Non-correlated Subqueries

Non-correlated subqueries allow each row from the containing SQL statement to be compared to a set of values. Non-correlated subqueries further divided 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

• Multiple-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, 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.

SELECT lname

FROM employee

WHERE salary > (SELECT AVG(salary)

                                     FROM EMPLOYEE);

subqueries can and can’t be utilized in SQL statements:

• The FROM clause may contain any type of non-correlated subquery.

• The SELECT and ORDER BY clauses may contain scalar subqueries.

• The GROUP BY clause may not contain subqueries.

• The START WITH and CONNECT BY clauses, used for querying hierarchical data, may contain subqueries.

Multiple Row subqueries

When a subquery returns more than one row, it is not possible to use only comparison operators, since a single value cannot be directly compared to a set of values. However, a single value can be compared to each value in a set. To accomplish this, the special keywords ANY and ALL may be used with comparison operators to determine if a value is equal to (or less than, greater than, etc.) any members of the set or all members of the set. Consider the following query:

SELECT fname, lname

FROM employee

WHERE dept_id = 3 AND salary >= ALL

                            (SELECT salary  FROM employee  WHERE dept_id = 3);

The next query uses the ANY operator to find all employees whose salary exceeds that of any top-level manager:

SELECT fname, lname

FROM employee WHERE manager_emp_id IS NOT NULL  AND salary > ANY

(SELECT salary  FROM employee  WHERE manager_emp_id IS NULL);

Multiple-Column Subqueries

While all of the previous examples compare a single column from the containing SQL statement to the result set returned by the subquery, it is also possible to issue a subquery against multiple columns. Consider the following UPDATE statement, which rolls up data from an operational table into an aggregate table:

UPDATE monthly_orders SET  tot_orders = (SELECT COUNT(*)   

FROM cust_order    

WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY')   AND

order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY') AND

cancelled_dt IS NULL),  max_order_amt = (SELECT MAX(sale_price)

FROM cust_order  

 WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY') AND 

order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY') AND 

cancelled_dt IS NULL),

min_order_amt = (SELECT MIN(sale_price)    

FROM cust_order    

WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY')      AND order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY')  AND

cancelled_dt IS NULL),  tot_amt = (SELECT SUM(sale_price)   

 FROM cust_order    

WHERE order_dt >= TO_DATE('01-NOV-2001','DD-MON-YYYY')  AND

 order_dt < TO_DATE('01-DEC-2001','DD-MON-YYYY') AND cancelled_dt IS NULL) 

WHERE month = 11 and year = 2001;