Union and Union All

Make Union builder’s row visible, when you want to concatenate fields from similar sets of data. For instance, the following queries combine the DEPTNO field of dept table (from Field) with all DEPTNO values from emp, and the singular constant value 50. Union All keeps all values, while Union (on the right) eliminates duplicates:

While you may apply the usual transformations to the first selection, union fields must come from tables not used anywhere else (emp table is used only for this purpose here). The optional Sort applies to the whole combined set.

Intersect and Except

All our databases support Intersect and Except! When they don’t already have native support for it, or when you turn the Always Emulate Intersect/Except option ON, we emulate them. [Interesting blog post about Intersect and Except]

Intersect shows only values common in all sets, while Except excludes from the first set values found also in the second set. Intersect All and Except All (if supported) do not remove duplicates. All department numbers are 10, 20, 30 and 40, but last department has no employees. As consequence, the queries below show 10, 20 and 30 as common values, and 40 the dept value not found in emp:

Constant Tables

Merge rows of constant values with Values, to generate dynamic virtual tables on the fly. FruitsV demo query uses this technique. When VALUES is not natively supported by the database (as in PostgreSQL, Amazon Redshift, SQL Server and DB2), or when you turn the Always Emulate Values setting ON, we transparently emulate it with UNION ALL (see FruitsU alternative demo query) for a similar result:

Queries using Values have several restrictions:

• all cells (from Field and Values) must have constant values (or query parameter names) and nothing else.
• cannot mix-up with other Union types (like Union, Union All, Intersect or Except).
• can use Sort, Filter, Selection, Summary and Highlight builder bars.
• cannot use Lookup, Formula, Where, Group and Having (for grouping), Partition, Sort Over and Rows (for window functions) builder bars.
• cannot have tables, views or other queries in the diagram area.
• all values from one column must have the same data type.
• cells left empty will be considered Null by default.