Examples of a fullselect

Example 1

Select all columns and rows from the EMPLOYEE table.

   SELECT * FROM EMPLOYEE

Example 2

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')

Example 3

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')

Example 4

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')

Example 5

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.

Example 6

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.

Example 7

Make the same query as in example 3, only include an additional two employees currently not in any table and tag these rows as "new".

   SELECT EMPNO, 'emp' FROM EMPLOYEE
     WHERE WORKDEPT LIKE 'E%'
   UNION
   SELECT EMPNO, 'empprojact' FROM EMPPROJACT
     WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')
   UNION
   VALUES ('NEWAAA', 'new'), ('NEWBBB', 'new')