Examples of a subselect

subselect can be used in many different ways.

Example 1

Select all columns and rows from the EMPLOYEE table.

   SELECT * FROM EMPLOYEE

Example 2

Join the EMPPROJACT and EMPLOYEE tables, select all the columns from the EMPPROJACT table and add the employee's surname (LASTNAME) from the EMPLOYEE table to each row of the result.

   SELECT EMPPROJACT.*, LASTNAME
     FROM EMPPROJACT, EMPLOYEE
     WHERE EMPPROJACT.EMPNO = EMPLOYEE.EMPNO

Example 3

Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO), employee surname (LASTNAME), department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the DEPARTMENT table) and department name (DEPTNAME) of all employees who were born (BIRTHDATE) earlier than 1930.

  SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
    FROM EMPLOYEE, DEPARTMENT
    WHERE WORKDEPT = DEPTNO
    AND YEAR(BIRTHDATE) < 1930

This subselect could also be written as follows:

  SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
    FROM EMPLOYEE INNER JOIN DEPARTMENT
    ON WORKDEPT = DEPTNO
    WHERE YEAR(BIRTHDATE) < 1930

Example 4

Select the job (JOB) and the minimum and maximum salaries (SALARY) for each group of rows with the same job code in the EMPLOYEE table, but only for groups with more than one row and with a maximum salary greater than or equal to 27000.

   SELECT JOB, MIN(SALARY), MAX(SALARY)
     FROM EMPLOYEE
     GROUP BY JOB
     HAVING COUNT(*) > 1 AND MAX(SALARY) >= 27000

Example 5

Select all the rows of EMPPROJACT table for employees (EMPNO) in department (WORKDEPT) ‘E11'. (Employee department numbers are shown in the EMPLOYEE table.)

   SELECT * FROM EMPPROJACT
     WHERE EMPNO IN (SELECT EMPNO FROM EMPLOYEE
                     WHERE WORKDEPT = 'E11')

Example 6

From the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary for all employees.

   SELECT WORKDEPT, MAX(SALARY)
     FROM EMPLOYEE
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                           FROM EMPLOYEE)

The subquery in the HAVING clause would only be executed once in this example.

Example 7

Using the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary in all other departments.

   SELECT WORKDEPT, MAX(SALARY)
     FROM EMPLOYEE EMP_COR
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                           FROM EMPLOYEE
                           WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)

In contrast to example 6, the subquery in the HAVING clause would need to be executed for each group.

Example 8

Join the EMPLOYEE and EMPPROJACT tables, select all of the employees and their project numbers. Return even those employees that do not have a project number currently assigned.

   SELECT EMPLOYEE.EMPNO, PROJNO
     FROM EMPLOYEE LEFT OUTER JOIN EMPPROJACT
     ON EMPLOYEE.EMPNO = EMPPROJACT.EMPNO

Any employee in the EMPLOYEE table that does not have a project number in the EMPPROJACT table will return one row in the result table containing the EMPNO value and the null value in the PROJNO column.