Set Operators

Set operators combine the results of two queries into a single result. Databend supports the following set operators:

• INTERSECT
• EXCEPT
• UNION [ALL]

INTERSECT

Returns all distinct rows selected by both queries.

Syntax

SELECT column1 , column2 ....
FROM table_names
WHERE condition

INTERSECT

SELECT column1 , column2 ....
FROM table_names
WHERE condition

Example

create table t1(a int, b int);
create table t2(c int, d int);

insert into t1 values(1, 2), (2, 3), (3 ,4), (2, 3);
insert into t2 values(2,2), (3, 5), (7 ,8), (2, 3), (3, 4);

select * from t1 intersect select * from t2;

Output:

2|3
3|4

EXCEPT

Returns All distinct rows selected by the first query but not the second.

Syntax

SELECT column1 , column2 ....
FROM table_names
WHERE condition

EXCEPT

SELECT column1 , column2 ....
FROM table_names
WHERE condition

Example

create table t1(a int, b int);
create table t2(c int, d int);

insert into t1 values(1, 2), (2, 3), (3 ,4), (2, 3);
insert into t2 values(2,2), (3, 5), (7 ,8), (2, 3), (3, 4);

select * from t1 except select * from t2;

Output:

1|2

UNION [ALL]

Combines rows from two or more result sets. Each result set must return the same number of columns, and the corresponding columns must have the same or compatible data types.

The command removes duplicate rows by default when combining result sets. To include duplicate rows, use UNION ALL.

Syntax

SELECT column1 , column2 ...
FROM table_names
WHERE condition

UNION [ALL]

SELECT column1 , column2 ...
FROM table_names
WHERE condition

[UNION [ALL]

SELECT column1 , column2 ...
FROM table_names
WHERE condition]...

[ORDER BY ...]

Example

CREATE TABLE support_team 
  ( 
     NAME   STRING, 
     salary UINT32 
  ); 

CREATE TABLE hr_team 
  ( 
     NAME   STRING, 
     salary UINT32 
  ); 

INSERT INTO support_team 
VALUES      ('Alice', 
             1000), 
            ('Bob', 
             3000), 
            ('Carol', 
             5000); 

INSERT INTO hr_team 
VALUES      ('Davis', 
             1000), 
            ('Eva', 
             4000); 

-- The following code returns the employees in both teams who are paid less than 2,000 dollars:

SELECT NAME AS SelectedEmployee, 
       salary 
FROM   support_team 
WHERE  salary < 2000 
UNION 
SELECT NAME AS SelectedEmployee, 
       salary 
FROM   hr_team 
WHERE  salary < 2000 
ORDER  BY selectedemployee DESC; 

Output:

Davis|1000
Alice|1000