UNION | INTERSECT | EXCEPT

UNION

UNION merges all rows in the result set of the first query with rows in the result set of the second query.

query UNION [ALL | DISTINCT] query

ALL and DISTINCT indicate whether duplicate rows are returned. ALL returns all rows. DISTINCT returns only one row. If this parameter is not specified, the default value DISTINCT is used.

INTERSECT

query INTERSECT [DISTINCT] query

INTERSECT returns only the rows that intersect the results of the first and second queries. The following is an example of one of the simplest INTERSECT clauses. It selects values 13 and 42 and merges this result set with the second query that selects value 13. Because 42 is only in the result set of the first query, it is not included in the final result:

SELECT * FROM (VALUES 13,42) INTERSECT SELECT 13;
 _col0 ------- 
    13
 (1 row)

EXCEPT

query EXCEPT [DISTINCT] query

EXCEPT returns rows that are in the first query result and not in the second query result.

SELECT * FROM (VALUES 13, 42) EXCEPT SELECT 13;
 _col0
-------
   42
(1 row)

Currently, the having clause does not support column aliases. For example:

select count(userid) as num ,dept as aaa from salary group by dept having aaa='d1';

The following error is reported:

Query 20210630_085136_00024_wc8n9@default@HetuEngine failed: line 1:75: Column 'aaa' cannot be resolved