Subqueries

What is a subquery

• A subquery is just a SELECT statement nested within another SQL statement.
• The inner query runs first, and its result feeds the outer query.
• Limit the number of nesting levels of subqueries whenever possible.
• Whenever possible, use a join instead of a subquery.

Subquery with the IN phrase

• Syntax: WHERE column-specification [NOT] IN (subquery).
• The subquery returns a list of values; the outer row qualifies if its value is in the list.
• Example:-
  -- Customers who have an invoice of 200 or more SELECT CUSTNO, FNAME, LNAME FROM MM01.CUSTOMER WHERE CUSTNO IN (SELECT INVCUST FROM MM01.INVOICE WHERE INVTOTAL >= 200); -- Employees earning more than the average salary SELECT EMPNO, FNAME, LNAME FROM EMPLOYEE WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE);

Subqueries with ANY, SOME and ALL

• Syntax: WHERE column operator {ANY | SOME | ALL} (subquery). The keyword is coded after the comparison operator.
• With ANY and SOME, the condition must be true for at least one of the values returned by the subquery.
• With ALL, the condition must be true for all of the values returned by the subquery.

Correlated subqueries

• Unlike a basic subquery, a correlated subquery does not work independently of the outer SELECT.
• It is performed once for each row the outer SELECT processes, and it references a column of the outer query.
• Because correlated subqueries often need substantial system resources, use a join or a basic subquery whenever possible.
• A subquery cannot be based on the same table that an INSERT, UPDATE or DELETE is modifying.