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.

    CREATE TABLE t1 (v VARCHAR);
    CREATE TABLE t2 (i INTEGER);
    INSERT INTO t1 (v) VALUES ('Adams, Douglas');
    INSERT INTO t2 (i) VALUES (42);
    
    -- This statement will fail due to the different data types.
    SELECT v FROM t1    -- VARCHAR
    UNION
    SELECT i FROM t2    -- INTEGER
    ;
    
    -- If the columns can be cast to the same data type, then you can use
    -- explicit casting to solve the problem:
    SELECT v::VARCHAR FROM t1
    UNION
    SELECT i::VARCHAR FROM t2;
    

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 ...