Less06Using Subqueries to Solve Queries

After completing this lesson, you should be able to do the following:

Define subqueries

Describe the types of problems that subqueries can solve

After completing this lesson, you should be able to do the following:

Define subqueries

Describe the types of problems that subqueries can solve

The subquery (inner query) executes once before the main query (outer query).

The result of the subquery is used by the main query.

SELECT last_name

FROM   employees

WHERE  salary >

               (SELECT salary

                FROM   employees

                WHERE  last_name = 'Abel');

Enclose subqueries in parentheses.

Place subqueries on the right side of the comparison condition.

The ORDER BY clause in the subquery is not needed unless you are performing Top-N analysis.

Use single-row operators with single-row subqueries, and use multiple-row operators with
multiple-row subqueries.

SELECT last_name, job_id, salary

FROM   employees

WHERE  job_id = 

                (SELECT job_id

                 FROM   employees

                 WHERE  employee_id = 141)

AND    salary >

                (SELECT salary

                 FROM   employees

                 WHERE  employee_id = 143);

SELECT last_name, job_id, salary

FROM   employees

WHERE  salary =

                (SELECT MIN(salary)

                 FROM   employees);

•The Oracle server executes subqueries first.

•The Oracle server returns results into the HAVING clause of the main query.

SELECT   department_id, MIN(salary)

FROM     employees

GROUP BY department_id

HAVING   MIN(salary) >

                       (SELECT MIN(salary)

                        FROM   employees

                        WHERE  department_id = 50);

SELECT employee_id, last_name

FROM   employees

WHERE  salary =

                (SELECT   MIN(salary)

                 FROM     employees

                 GROUP BY department_id);

Return more than one row

Use multiple-row comparison operators

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary < ANY

                    (SELECT salary

                     FROM   employees

                     WHERE  job_id = 'IT_PROG')

AND    job_id <> 'IT_PROG';

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary < ALL

                    (SELECT salary

                     FROM   employees

                     WHERE  job_id = 'IT_PROG')

AND    job_id <> 'IT_PROG';

SELECT emp.last_name

FROM   employees emp

WHERE  emp.employee_id NOT IN

                           (SELECT mgr.manager_id

                            FROM   employees mgr);

This practice covers the following topics:

Creating subqueries to query values based on unknown criteria

Using subqueries to find out which values exist in one set of data and not in another