Examples of subselects
You can use the various clauses of the subselect to construct queries.
The following example subselects illustrate how to use the various clauses of the subselect to construct queries.
SELECT * FROM DSN8C10.EMP;
SELECT JOB, MAX(SALARY), MIN(SALARY)
FROM DSN8C10.EMP
GROUP BY JOB
HAVING COUNT(*) > 1 AND MAX(SALARY) > 50000;
SELECT EMPNO, ACTNO, CHAR(EMSTDATE,USA), CHAR(EMENDATE,USA)
FROM DSN8C10.EMPPROJACT
WHERE EMPNO IN (SELECT EMPNO FROM DSN8C10.EMP
WHERE WORKDEPT = 'E11');
SELECT WORKDEPT, MAX(SALARY)
FROM DSN8C10.EMP
GROUP BY WORKDEPT
HAVING MAX(SALARY) < (SELECT AVG(SALARY)
FROM DSN8C10.EMP);
SELECT WORKDEPT, MAX(SALARY)
FROM DSN8C10.EMP Q
GROUP BY WORKDEPT
HAVING MAX(SALARY) < (SELECT AVG(SALARY)
FROM DSN8C10.EMP
WHERE NOT WORKDEPT = Q.WORKDEPT);
SELECT HIREYEAR, AVG(SALARY)
FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
FROM DSN8C10.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.
SELECT EMPNO, FIRSTNME, LASTNAME
FROM DSN8C10.EMP
ORDER BY HIREDATE;
(SELECT * FROM T1 ORDER BY C1)
UNION
(SELECT * FROM T2 ORDER BY C2);
(SELECT * FROM T1 ORDER BY C1)
UNION
SELECT * FROM T2 ORDER BY C2
SELECT *
FROM (SELECT * FROM T1
UNION ALL
(SELECT * FROM T2 ORDER BY 1)
) AS UTABLE
ORDER BY ORDER OF UTABLE;
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;
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);
SELECT EMPNO, FIRSTNME, LASTNAME
FROM DSN8C10.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
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 two things about the 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.
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
FROM PARTS INNER JOIN PRODUCTS
ON PARTS.PROD# = PRODUCTS.PROD#
AND SUPPLIER NOT LIKE 'A%';
PART SUPPLIER PROD# PRODUCT
======= ============ ===== ==========
MAGNETS BATEMAN 10 GENERATOR
PLASTIC PLASTIK_CORP 30 RELAY
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.
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.
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.
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
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)
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: Examples of cross join to combine information for all customers with all states.
- 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: Example of using 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));