Tutorial S3

Contents

• Subqueries
• ANY and ALL
• IN and NOT IN
• EXISTS and NOT EXISTS
• UNION of subqueries

Subqueries

Nesting of queries is accomplished in SQL by means of a search condition feature known as the subquery. A subquery is a subselect used in a predicate of a search condition. Multiple levels of nesting are permitted. It is often possible to frame a query either by using subqueries or by using joins between the tables.

Some students find subqueries easier to understand than using joins. So if you had difficulty with joins in tutorial 2 you might find this tutorial a lot easier.

The following example was given in tutorial 2 using a self join :-

List the surname and forename of all the employees who work in the same department as employee number 16.

SELECT x.surname, x.forenames
FROM employee x, employee y
WHERE x.depno = y.depno
AND y.empno = 16
AND x.empno != 16

This could be implemented using a subquery as :-

SELECT surname, forenames
FROM employee
WHERE depno =
   (SELECT depno
    FROM employee
    WHERE empno = 16)
AND empno != 16

The subquery in the brackets is evaluated first. The value in SELECT clause is then placed in the outer query, which is then evaluated. So that if the subquery established that employee 16 worked in department number 5, the following outer query would then be evaluated.

SELECT surname, forenames
FROM employee 
WHERE depno = 5
AND empno != 16

The SELECT clause of a SUBQUERY can return ONLY ONE field name which may be associated with zero, one or many values.

Notice also in the previous example that although there are two different occurrences of the table employee, they need not be given aliases. This is because the definition of employee in each  FROM clause above, is only referred to locally within the predicates of the corresponding SELECT clause.

Aliases may be optionally used as shorthand or to clarify statements. However, at times, it is essential to use an alias, for example to reference a table defined in an outer query. In the following example, if there was no explicit reference to x.depno in the subquery, then it would assumed to be implicitly qualified by y.depno.

Unqualified columns in a subquery are looked up in the tables of that subquery, then in the table of the next enclosing query and so on.

The overall query is evaluated by letting x take each of its permitted values in turn ( i.e. letting it range over the employee table), and for each such value of x, evaluating the subquery.

This type of query must be done by using subqueries and cannot be done just using joins.

List the employee's number, name and department for any employee with a current salary greater than the average current salary for their department.

SELECT x.empno, x.surname, x.depno
FROM employee x, jobhistory
WHERE enddate IS NULL
AND x.empno = jobhistory.empno
AND salary >
   (SELECT AVG(salary)
    FROM employee y, jobhistory
    WHERE y.empno = jobhistory.empno
    AND enddate IS NULL
    AND y.depno = x.depno)

Notice that there need be no correlation names for the jobhistory tables as they are only used locally and therefore are implicit.

The following examples cover predicates which are used in combination with subqueries. They specify how values returned by a subquery are to be used in the outer WHERE clause.

ANY and ALL

Any or ALL can be inserted between the comparison operator (=, !=, >, >=, <, <=) and the subquery.

List the employees who earn more than any employee in Department 5 :-.

SELECT employee.empno, surname, salary
FROM employee, jobhistory
WHERE enddate IS NULL
AND employee.empno = jobhistory.empno
AND salary > ANY
     (SELECT salary
      FROM employee, jobhistory
      WHERE enddate IS NULL
      AND depno=5
      AND employee.empno = jobhistory.empno)

The lowest salary in department 5 is £17000, employee 29, the main query then returns employees who earn more than £17000.

List the employees who earn more than all the employees in Department 5 :-

SELECT employee.empno, surname, salary
FROM employee, jobhistory
WHERE enddate IS NULL
AND employee.empno = jobhistory.empno
AND salary > ALL
    (SELECT salary
     FROM employee, jobhistory
     WHERE enddate IS NULL
     AND depno=5
     AND employee.empno = jobhistory.empno)

Since the greatest salary in department 5 is £29000 , employee number 28, the main query returns all employees who earn more than £29000.

IN and NOT IN

Subqueries can return a list of values. IN and NOT IN are used to check if values are in this list.

List all the employee numbers of anyone who has been on a course in 1988.

SELECT empno
FROM empcourse
WHERE courseno IN
      (SELECT courseno
       FROM course
       WHERE cdate BETWEEN '01-JAN-1988' AND '31-DEC-1988')

Notice the subquery must contain a reference to exactly one column in its SELECT clause.

EXISTS and NOT EXISTS

EXISTS evaluates to true if and only if the set represented by the subquery is nonempty.

List all the employees who have at least one other employee currently doing the same job as them.

SELECT x.empno, surname, x.position
FROM jobhistory x, employee
WHERE x.empno = employee.empno
AND x.enddate IS NULL
AND EXISTS
  (SELECT *
   FROM jobhistory y
   WHERE y.enddate IS NULL
   AND y.position = x.position
   AND x.empno != y.empno)

UNION of subqueries

A query may be composed of two or more queries with the operator UNION.

UNION returns all the distinct rows returned by either of the queries it applies to. This means it removes all duplicates.

UNION ALL returns all rows returned by either of the queries it applies to. Duplicates allowed.

List all employees who are in department 4 or 5.

SELECT forenames, surname
FROM employee
WHERE depno = 4
UNION
SELECT forename, surname
FROM employee
WHERE depno = 5

This UNION could have been done more concisely by using a IN clause.

SELECT forenames, surname
FROM employee
WHERE depno IN (4, 5)

However, this is not as easy if the two parts of the query are from different tables.

List all employees who were born before 1960 or who earn more than £25000.

SELECT forenames, surname
FROM employee
WHERE dob < ‘01-JAN-1960’
UNION
SELECT forenames, surname
FROM employee, jobhistory
WHERE employee.empno = jobhistory.empno
AND enddate IS NULL
AND salary > 25000

Any employee who meets both conditions is listed only once.