# Query

A Query creates a virtual table based on existing tables or constants built into tables.

## Syntax

```{
( Query
[ ORDER BY clause ]
[ result offset clause ]
[ fetch first clause ]
) |
Query EXCEPT [ ALL | DISTINCT ] Query |
Query UNION [ ALL | DISTINCT ] Query |
VALUES Expression
}```

You can arbitrarily put parentheses around queries, or use the parentheses to control the order of evaluation of the `UNION` operations. These operations are evaluated from left to right when no parentheses are present.

## Duplicates in UNION and EXCEPT ALL results

The `ALL` and `DISTINCT` keywords determine whether duplicates are eliminated from the result of the operation. If you specify the `DISTINCT` keyword, then the result will have no duplicate rows. If you specify the `ALL` keyword, then there may be duplicates in the result, depending on whether there were duplicates in the input. `DISTINCT` is the default, so if you don’t specify `ALL` or `DISTINCT`, the duplicates will be eliminated. For example, `UNION` builds an intermediate ResultSet with all of the rows from both queries and eliminates the duplicate rows before returning the remaining rows. `UNION ALL` returns all rows from both queries as the result.

Depending on which operation is specified, if the number of copies of a row in the left table is L and the number of copies of that row in the right table is R, then the number of duplicates of that particular row that the output table contains (assuming the `ALL` keyword is specified) is:

• `UNION`: ( L + R ).
• `EXCEPT`: the maximum of ( L - R ) and 0 (zero).

## Examples

Here’s a simple `SELECT` expression:

``````SELECT *
FROM ORG;
``````

Here’s a `SELECT` with a subquery:

``````SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS;
``````

Here’s a `SELECT` with a subquery:

``````SELECT *
FROM (SELECT CLASS_CODE FROM CL_SCHED) AS CS;
``````

Here’s a `UNION` that lists all employee numbers from certain departments who are assigned to specified project numbers:

``````SELECT EMPNO, 'emp'
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%'
UNION
SELECT EMPNO, 'emp_act'
FROM EMP_ACT
WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112');
``````