Subquery

 

Overview
Subqueries can be used to answer queries such as "who has a salary more than Tom's". For such query, two queries have to be executed: the first query finds Tom's salary and the second finds those whose salary is greater than Tom's. Subquery is an approach provides the capability of embedding the first query into the other:  Oracle executes the subquery first, making the result of the sub query available to the main query and then executing the main query.

The syntax of subquery is

SELECT <column, ...>

FROM <table>

WHERE expression operator

    ( SELECT <column, ...>

       FROM <table>

      WHERE <condition>)

For example, the following statement answers the described query above.

    SQL> SELECT * FROM employee

         WHERE sal > (SELECT sal WHERE name='TOM');

Note that:

a subquery must be enclosed in the parenthesis.
a subquery must be put in the right hand of the comparison operator, and
a subquery cannot contain a ORDER-BY clause.
a query can contain more than one sub-queries.
In general, there are three types subqueries:

single-row subquery, where the subquery returns only one row.
multiple-row subquery, where the subquery returns multiple rows,.and
multiple column subquery, where the subquery returns multiple columns.
Single-row subqueries can only be used with single-row comparison operators, and multiple-row subqueries can be used only with multiple-row operators.  They are to be described separately in the following.

--------------------------------------------------------------------------------

Single-Row Subquery
The operators that can be used with single-row subqueires are =, >, >=, <, <=, and <>.

Group functions can be used in the subquery. For example, the following statement retrieve the details of the employee holding the highest salary.

SQL> SELECT * FROM employee

     WHERE sal = (SELECT MIN(sal) FROM employee);

Having-clause can also be used with the single-row subquery. For example, the following statement returns all departments in where the minimum salary is more than the minimum salary in the department 5.

SQL> SELECT dept_no, MIN(sal) FROM employee

     GROUP BY dept_no

  HAVING MIN(sal) > (

      SELECT MIN(sal)

        FROM employee

        WHERE dept_no = 5);

--------------------------------------------------------------------------------

Multiple-Row Subquery
Note the following statement is illegal, because the operator = cannot be used with subquery returns multiple rows.

SQL> SELECT name, sal FROM employee

  WHERE sal > (

      SELECT MIN(sal) FROM employee GROUP BY dept_no);

Some operators that can be used with multipe-row subqueries are:

IN, equal to any member in the list,
ANY, compare values to each value returned by the subquery.
For example, the following statement find the employees whose salary is the same as the minimum salary of the employees in some department.

SQL>  SELECT name, sal FROM employee

  WHERE sal IN (

      SELECT MIN(sal)

        FROM employee

        GROUP BY dept_no);

 

For example, the following statement find the employees whose salary is more than the minimum salary of the employees in any department.

SQL> SELECT name, sal FROM employee

  WHERE sal > ANY (

      SELECT MIN(sal)

        FROM employee

        GROUP BY dept_no);

--------------------------------------------------------------------------------

Multiple-Column Subquery
In multiple-column  subqueries, rows in the subquery results are evaluated in the main query in pair-wise comparison. That is, column-to-column comparison and row-to-row comparison.

For example, the following statement lists all items whose quantity and product id match to an item of order id 200.

SQL> SELECT order_id,  product_id, quantity

    FROM item

    WHERE (product_id, quantity) IN (

        SELECT  product_id, quantity FROM item WHERE order_it = 200)

    AND order_id = 200;

Note that you can put a subquery in the FROM clause in the main query.

For example, the following statement finds all employees in each department where their salary is above the average.

    SQL> SELECT a.name, a.sal, a.dept_no, b.salary_ave

        FROM employee a,

               (SELECT dept_no, AVE(sal) salary_ave

              FROM employee 

                GROUP BY dept_no)

        WHERE  a.dept_no = b.dept_no;

        AND a.sal > b.salary_ave;

--------------------------------------------------------------------------------

from ; http://www.comp.nus.edu.sg/~ooibc/courses/sql/dml_query_subquery.htm