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