UNION and EXCEPT

You can combine the results of two queries with the keywords UNION and EXCEPT. To do this, the SELECT clauses of both queries have to be compatible. This means that they have to be the same size, i.e., result in tables with the same number of columns and the respective column types have to match. The types must also be the same simple type, or both object types, or both relation types.

You can therefore also connect object types that are quite different with UNION, e.g., an unversioned with a versioned object type.

SELECT [p] FROM [dms]:[person] AS [p]
UNION
SELECT [m] FROM [ecm]:[invoice] AS [m]

Sometimes you want to link queries which have SELECT clauses that are actually incompatible. Each type is compatible with UNION, so you can adapt both SELECT clauses in order to make both queries compatible. In this way,

 SELECT [p] FROM [dms]:[person] AS [p]

and

SELECT [sysid], [sysitemid] FROM [dms]:[invoice]

can be reformulated to

SELECT [p], null, null FROM [dms]:[person] AS [p]
UNION
SELECT null, [sysid], [sysitemid] FROM [dms]:[invoice]

A UNION link does not contain any duplicates. Adding ALL after UNION prevents duplicates from being filtered out. It is recommended that you always add ALL if a duplicate-free result table is not expressly required.

Checking for duplicates requires additional effort and therefore time. In particular, if it is clear from the start that there cannot be any duplicates, e.g., if two different types are linked, a UNION ALL should be preferred to the simple UNION.

SELECT [p] FROM [dms]:[person] AS [p]
UNION ALL
SELECT [m] FROM [dms]:[invoice] AS [m]

In the same way as the UNION operator, the EXCEPT operator links two SELECT queries. Here the results of the second query are removed from those of the first.

The following example illustrates this: The query only returns the names of customers who are not listed in the directory. Even a name that appears more frequently for customers and is not in the directory will only appear once in the result table.

SELECT [name] FROM [kunde]
EXCEPT
SELECT [name] FROM [directory]

It is possible to combine UNION and EXCEPT. The result will never contain duplicates. The following example illustrates this: The query only returns the names of customers and persons who are not listed in the directory.

SELECT [name] FROM [kunde]
UNION
SELECT [name] FROM [person]
EXCEPT
SELECT [name] FROM [directory]

For object types or relation types, a SELECT only makes sense if the same types actually do appear in both queries. If the types are different and none is a derivation of the other, the following query is pointless.

SELECT [p] FROM [dms]:[person] AS [p]
EXCEPT
SELECT [m] FROM [dms]:[invoice] AS [m]

If both types are the same, it is better to combine both WHERE clauses into one.

Therefore

SELECT [p] FROM [dms]:[invoice] AS [p] WHERE <condition1>
EXCEPT                     
SELECT [p] FROM [dms]:[invoice] AS [p] WHERE <condition2>

can be reformulated to

SELECT [p]
FROM [dms]:[invoice] AS [p]
WHERE <condition1> AND NOT <condition2>

Particularly regarding object and relation types, a SELECT takes time and should only be used if the request cannot be formed differently.

Here is another example in which a reformulation is not straightforward. Let's assume that you want to identify all objects in a particular folder, except invoices with an amount less than 100.

SELECT [o]
FROM [sysFolder] [f] JOIN [dms]:[sysParent2Child] JOIN [dms]:[sysObject] [o]
WHERE [f].[sysitemid]=id'ABCDEF12345678901234567890123456'
SELECT [r]
FROM [invoice] AS [r] WHERE [amount] < 100