Select all columns and rows from the EMPLOYEE table.
SELECT * FROM EMPLOYEE
List the employee numbers (EMPNO) of all employees in the EMPLOYEE table whose department number (WORKDEPT) either begins with 'E' or who are assigned to projects in the EMPPROJACT table whose project number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'.
SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT LIKE 'E%' UNION SELECT EMPNO FROM EMPPROJACT WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
Make the same query as in example 2, only use UNION ALL so that no duplicate rows are eliminated.
SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT LIKE 'E%' UNION ALL SELECT EMPNO FROM EMPPROJACT WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
Make the same query as in example 2, and, in addition, "tag" the rows from the EMPLOYEE table with 'emp' and the rows from the EMPPROJACT table with 'empprojact'. Unlike the result from example 2, this query may return the same EMPNO more than once, identifying which table it came from by the associated "tag".
SELECT EMPNO, 'emp' FROM EMPLOYEE WHERE WORKDEPT LIKE 'E%' UNION SELECT EMPNO, 'empprojact' FROM EMPPROJACT WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
This example of EXCEPT produces all rows that are in T1 but not in T2, with duplicate rows removed.
(SELECT * FROM T1) EXCEPT DISTINCT (SELECT * FROM T2)
If no NULL values are involved, this example returns the same results as:
(SELECT DISTINCT * FROM T1 WHERE NOT EXISTS (SELECT * FROM T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...) )
where C1, C2, and so on represent the columns of T1 and T2.
This example of INTERSECT produces all rows that are in both tables T1 and T2, with duplicate rows removed.
(SELECT * FROM T1) INTERSECT DISTINCT (SELECT * FROM T2)
If no NULL values are involved, this example returns the same results as:
(SELECT DISTINCT * FROM T1 WHERE EXISTS (SELECT * FROM T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND...) )
where C1, C2, and so on represent the columns of T1 and T2.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.