Examples for subselect

You can use the various clauses of the subselect to construct queries.

Examples

The following subselect example illustrate how to use the various clauses of the subselect to construct queries.

Example 1: Show all rows of the table DSN8D10.EMP.
```
   SELECT * FROM DSN8D10.EMP;
```
Example 2: Show the job code, maximum salary, and minimum salary for each group of rows of DSN8D10.EMP with the same job code, but only for groups with more than one row and with a maximum salary greater than 50000.
```
   SELECT JOB, MAX(SALARY), MIN(SALARY)
     FROM DSN8D10.EMP
     GROUP BY JOB
     HAVING COUNT(*) > 1 AND MAX(SALARY) > 50000;
```
Example 3: For each employee in department E11, get the following information from the table DSN8D10.EMPPROJACT: employee number, activity number, activity start date, and activity end date. Using the CHAR function, convert the start and end dates to their USA formats. Get the needed department information from the table DSN8D10.EMP.
```
   SELECT EMPNO, ACTNO, CHAR(EMSTDATE,USA), CHAR(EMENDATE,USA)
     FROM DSN8D10.EMPPROJACT
     WHERE EMPNO IN (SELECT EMPNO FROM DSN8D10.EMP
                     WHERE WORKDEPT = 'E11');
```
Example 4: Show the department number and maximum departmental salary for all departments whose maximum salary is less than the average salary for all employees. (In this example, the subquery would be executed only one time.)
```
   SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8D10.EMP
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8D10.EMP);
```
Example 5: Show the department number and maximum departmental salary for all departments whose maximum salary is less than the average salary for employees in all other departments. (In contrast to Example 4, the subquery in this statement, containing a correlated reference, would need to be executed for each group.)
```
   SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8D10.EMP Q
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8D10.EMP
                             WHERE NOT WORKDEPT = Q.WORKDEPT);
```
Example 6: For each group of employees hired during the same year, show the year-of-hire and current average salary. (This example demonstrates how to use the AS clause in a FROM clause to name a derived column that you want to refer to in a GROUP BY clause.)
```
   SELECT HIREYEAR, AVG(SALARY)
     FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
             FROM DSN8D10.EMP) AS NEWEMP
     GROUP BY HIREYEAR;
```
Example 7: For an example of how to group the results of a query by an expression in the SELECT clause without having to retype the expression, see Example 4 for CASE expressions.
Example 8: Get the employee number and employee name for all the employees in DSN8D10.EMP. Order the results by the date of hire.
```
   SELECT EMPNO, FIRSTNME, LASTNAME
     FROM DSN8D10.EMP
     ORDER BY HIREDATE;
```
Example 9: Select all the rows from tables T1 and T2 and order the rows such that the rows from table T1 are first and are ordered by column C1, followed by the rows from T2, which are ordered by column C2. The rows of T1 are retrieved by one subselect which is connected to the results of another subselect that retrieves the rows from T2. Each subselect specifies the ordering for the rows from the referenced table. Note that both subselects need to be enclosed in parenthesis because each subselect is not the outermost fullselect. Because each of the two ORDER BY clauses appears in a parenthesized subselect, neither ORDER BY clause provides an ordering for the outermost result table.
```
(SELECT * FROM T1 ORDER BY C1)
UNION
(SELECT * FROM T2 ORDER BY C2);
```
Example 10: Specify the ORDER BY clause to order the results of a union using the second column of the result table if the union. In this example, the second ORDER BY clause applies to the results of the outermost fullselect (the result of the union) rather than to the second subselect. If the intent is to apply the second ORDER BY clause to the second subselect, the second subselect should be enclosed within parentheses as shown in Example 9.
```
(SELECT * FROM T1 ORDER BY C1)
UNION
SELECT * FROM T2 ORDER BY C2
```
Example 11: Retrieve all rows of table T1 with no specific ordering) and connect the result table to the rows of table T2, which have been ordered by the first column of table T2. The ORDER BY ORDER OF clause in the fullselect specifies that the order of the rows in the result table of the union is to be inherited by the final result.
```
SELECT *                             
  FROM (SELECT * FROM T1             
         UNION ALL                   
        (SELECT * FROM T2 ORDER BY 1)
       ) AS UTABLE                   
  ORDER BY ORDER OF UTABLE;            
```
Example 12: The following example uses a query to join data from a table to the result table of a nested table expression. The query uses the ORDER BY ORDER OF clause to order the rows of the result table using the order of the rows of the nested table expression.
```
SELECT T1.C1, T1.C2, TEMP.Cy, TEMP.Cx
  FROM T1,
    (SELECT T2.C1, T2.C2 FROM T2 ORDER BY 2) AS TEMP(Cx, Cy)
  WHERE Cy = T1.C1
  ORDER BY ORDER OF TEMP;
```

Example 13: Using the EMP_ACT table, find the project numbers that have an employee whose salary is in the top three salaries for all employees.

SELECT EMP_ACT.EMPNO, PROJNO
  FROM EMP_ACT
  WHERE EMP_ACT.EMPNO IN
    (SELECT EMPLOYEE.EMPNO
     FROM EMPLOYEE
     ORDER BY SALARY DESC
     FETCH FIRST 3 ROWS ONLY);

Example 14: Assume that an external function named ADDYEARS exists. For a given date, the function adds a given number of years and returns a new date. (The data types of the two input parameters to the function are DATE and INTEGER.) Get the employee number and employee name for all employees who have been hired within the last 5 years.

   SELECT EMPNO, FIRSTNME, LASTNAME
     FROM DSN8D10.EMP
     WHERE ADDYEARS(HIREDATE, 5) > CURRENT DATE;

To distinguish the different types of joins, to show nested table expressions, and to demonstrate how to combine join columns, the remaining examples use these two tables:

The PARTS table                         The PRODUCTS table
PART      PROD#   SUPPLIER              PROD#     PRODUCT        PRICE
=======   =====   ============          =====     ===========    =====
WIRE      10      ACWF                  505       SCREWDRIVER    3.70
OIL       160     WESTERN_CHEM          30        RELAY          7.55
MAGNETS   10      BATEMAN               205       SAW            18.90
PLASTIC   30      PLASTIK_CORP          10        GENERATOR      45.75
BLADES    205     ACE_STEEL

Example 15: Join the tables on the PROD# column to get a table of parts with their suppliers and the products that use the parts:
```
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
   FROM PARTS, PRODUCTS
   WHERE PARTS.PROD# = PRODUCTS.PROD#;
```
or
```
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;
```
Either one of these two statements give this result:
```
PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
```
Notice the following things about this example:
- There is a part in the parts table (OIL) whose product (#160) is not listed in the products table. There is a product (SCREWDRIVER, #505) that has no parts listed in the parts table. Neither OIL nor SCREWDRIVER appears in the result of the join.
  An outer join, however, includes rows where the values in the joined columns do not match.
- There is explicit syntax to express that this familiar join is not an outer join but an inner join. You can use INNER JOIN in the FROM clause instead of the comma. Use ON when you explicitly join tables in the FROM clause.
You can specify more complicated join conditions to obtain different sets of results. For example, eliminate the suppliers that begin with the letter A from the table of parts, suppliers, product numbers and products:
```
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
    AND SUPPLIER NOT LIKE 'A%';
```
The result of the query is all rows that do not have a supplier that begins with A:
```
PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
```

Example 16: Join the tables on the PROD# column to get a table of all parts and products, showing the supplier information, if any.

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

The result is:

PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       (null)
(null)      (null)         (null)    SCREWDRIVER

The clause FULL OUTER JOIN includes unmatched rows from both tables. Missing values in a row of the result table are filled with nulls.

Example 17: Join the tables on the PROD# column to get a table of all parts, showing what products, if any, the parts are used in:

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS LEFT OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

The result is:

PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ==========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       (null)

The clause LEFT OUTER JOIN includes rows from the table identified before it where the values in the joined columns are not matched by values in the joined columns of the table identified after it.

Example 18: Join the tables on the PROD# column to get a table of all products, showing the parts used in that product, if any, and the supplier.

SELECT PART, SUPPLIER, PRODUCTS.PROD#, PRODUCT
  FROM PARTS RIGHT OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

The result is:

PART        SUPPLIER       PROD#     PRODUCT
=======     ============   =====     ===========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
(null)      (null)         505       SCREWDRIVER

The clause RIGHT OUTER JOIN includes rows from the table identified after it where the values in the joined columns are not matched by values in the joined columns of the table identified before it.

Example 19: The result of Example 16 (a full outer join) shows the product number for SCREWDRIVER as null, even though the PRODUCTS table contains a product number for it. This is because PRODUCTS.PROD# was not listed in the SELECT list of the query. Revise the query using COALESCE so that all part numbers from both tables are shown.

SELECT PART, SUPPLIER,
      COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM, PRODUCT
      FROM PARTS FULL OUTER JOIN PRODUCTS
        ON PARTS.PROD# = PRODUCTS.PROD#;

In the result, notice that the AS clause (AS PRODNUM), provides a name for the result of the COALESCE function:

PART        SUPPLIER       PRODNUM   PRODUCT
=======     ============   =======   ===========
WIRE        ACWF           10        GENERATOR
MAGNETS     BATEMAN        10        GENERATOR
PLASTIC     PLASTIK_CORP   30        RELAY
BLADES      ACE_STEEL      205       SAW
OIL         WESTERN_CHEM   160       (null)
(null)      (null)         505       SCREWDRIVER

Example 20: For all parts that are used in product numbers less than 200, show the part, the part supplier, the product number, and the product name. Use a nested table expression.

SELECT PART, SUPPLIER, PRODNUM, PRODUCT
  FROM (SELECT PART, PROD# AS PRODNUM, SUPPLIER
                FROM PARTS
                   WHERE PROD# < 200) AS PARTX
            LEFT OUTER JOIN PRODUCTS
                ON PRODNUM = PROD#;

The result is:

PART        SUPPLIER         PRODNUM     PRODUCT
=======     ============     =======     ==========
WIRE        ACWF             10          GENERATOR
MAGNETS     BATEMAN          10          GENERATOR
PLASTIC     PLASTIK_CORP     30          RELAY
OIL         WESTERN_CHEM     160         (null)

Example 21: Examples of statements with DISTINCT specified more than once in a subselect:

   SELECT DISTINCT COUNT(DISTINCT A1), COUNT(A2)
     FROM T1;

   SELECT COUNT(DISTINCT A))
     FROM T1
     WHERE A3 > 0
     HAVING AVG(DISTINCT A4) >1;

Example 22: Use a cross join to combine information for all customers with all of the states. The cross join combines all rows in both tables and creates a Cartesian product. Assume that the following tables exist:

Customer:
---------------------
ACOL1     |  ACOL2
---------------------
A1        |  AA1
A2        |  AA2
A3        |  AA3
---------------------

States:
---------------------
BCOL1     |  BCOL2
---------------------
B1        |  BB1
B2        |  BB2
---------------------

The following two select statements produce identical results:

SELECT * FROM customer CROSS JOIN states

SELECT * FROM A, B

The result table for either of these select statements looks like the following:

------------------------------------------
ACOL1     |  ACOL2  |  BCOL1   |  BCOL2
------------------------------------------
A1        |  AA1    |  B1      |  BB1
A1        |  AA1    |  B2      |  BB2
A2        |  AA2    |  B1      |  BB1
A2        |  AA2    |  B2      |  BB2
A3        |  AA3    |  B1      |  BB1
A3        |  AA3    |  B2      |  BB2
------------------------------------------

Example 22: Use a typed-correlation-clause when referencing a generic table function. In the following select statement, 'tf6' is a generic table function defined using the CREATE FUNCTION (external table) statement. The typed-correlation-clause is used to define the column names and data types of the result table.
```
SELECT c1, c2
	FROM T1(tf6('abcd'))
		AS z (c1 int, c2 varchar(100));
```