Query Operators

Query operators allow queries to be combined using set operators.

In this Topic:

General Syntax

[ ( ] <query> [ ) ] { INTERSECT | MINUS | UNION [ ALL ] } [ ( ] <query> [ ) ]
[ ORDER BY ... ]
[ LIMIT ... ]

General Usage Notes

  • Each query may itself contain query operators, to allow any number of query expressions to be combined with set operators.
  • The ORDER BY and LIMIT / FETCH clauses are applied to the result of the set operator.
  • When using these operators, make sure that the data types of each column are consistent across the rows from different sources. One of the examples in the Examples section below illustrates the potential problem and solution when data types do not match.

INTERSECT

Returns rows from one query’s result set which also appear in another query’s result set, with duplicate elimination.

Syntax

SELECT ...
INTERSECT
SELECT ...

MINUS , EXCEPT

Removes rows from one query’s result set which appear in another query’s result set, with duplicate elimination.

The MINUS and EXCEPT keywords have the same meaning and can be used interchangeably.

Syntax

SELECT ...
MINUS
SELECT ...

SELECT ...
EXCEPT
SELECT ...

UNION [ ALL ]

Combines the result sets from two queries:

  • UNION combines with duplicate elimination.
  • UNION ALL combines without duplicate elimination.

The default is UNION (i.e. duplicate elimination).

Syntax

SELECT ...
UNION [ ALL ]
SELECT ...

Examples

UNION Example

This is a simple example of UNION. This also shows a potential problem and solution when data types do not match.

Start by creating the tables and inserting some data.

CREATE TABLE t1 (v VARCHAR);
CREATE TABLE t2 (i INTEGER);
INSERT INTO t1 (v) VALUES ('Adams, Douglas');
INSERT INTO t2 (i) VALUES (42);

Try to execute a UNION operation with different data types:

SELECT v FROM t1    -- VARCHAR
UNION
SELECT i FROM t2    -- INTEGER
;

Output:

100038 (22018): Numeric value 'Adams, Douglas' is not recognized

Now use explicit casting to convert the data to a compatible type:

SELECT v::VARCHAR FROM t1
UNION
SELECT i::VARCHAR FROM t2;

Output:

+----------------+
| V::VARCHAR     |
|----------------|
| Adams, Douglas |
| 42             |
+----------------+