Appearance

Reference>Meta Database>SQL Command Reference>Query Operators>Set Operators

Set Operators ​

Set operators can be used to combine queries.

Syntax ​

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

Usage Notes ​

  • Any number of query expressions can be combined with set operators, as each query can contain query operators itself.
  • The ORDER BY and LIMIT / FETCH clauses are applied to the result of the set operator.
  • Some scenarios that can lead to inconsistent data when using set operators:
    • The queries being combined return a different number of columns.
    • The data types of the individual columns in all rows of the different queries are inconsistent.
    • The "semantic meanings" of the columns in different queries do not match.
    • Using the * operator in a SELECT statement when the number of columns in the objects is the same, but the order of the columns differs.

Examples

Swapped columns:

sql
SELECT Product_Name, Product_Category FROM Store
UNION ALL
SELECT Product_Category, Product_Name FROM Sales;

Using the * operator:

sql
SELECT * FROM object1
UNION ALL
SELECT * FROM object2;

Column names in the output are determined by the names of the first query:

sql
SELECT Product_Name, Product_Category FROM Store
UNION ALL
SELECT Product_Category, Product_Name FROM Sales;

This query can also be rewritten as follows to illustrate the risk:

sql
SELECT Product_Category, Product_Name FROM Store
UNION ALL
SELECT Product_Name AS Product_Category, Product_Category AS Product_Name FROM Sales;

Operator Precedence ​

The operator precedence for set operators follows the ANSI and ISO SQL standards:

  • INTERSECT takes precedence over UNION [ALL] and MINUS.
  • UNION [ALL] and MINUS have the same priority.
  • Operators with the same priority are processed from left to right. To enforce a different order, parentheses can be used.

INTERSECT ​

INTERSECT creates the intersection of the rows from queries, eliminating duplicates.

Syntax ​

sql
SELECT ...  -- Query 1
INTERSECT
SELECT ...  -- Query 2

MINUS ​

Removes overlapping rows of the queries from the result set of the first query.

Syntax ​

sql
SELECT ...   -- Query 1
MINUS        
SELECT ...   -- Query 2

UNION [ALL] ​

UNION [ALL] combines the result sets from two queries. Specifically:

  • UNION removes duplicates.
  • UNION ALL combines without removing duplicates.

Syntax ​

sql
SELECT ...   -- Query 1
UNION
SELECT ...   -- Query 2

-- or 

SELECT ...   -- Query 1
UNION ALL
SELECT ...   -- Query 2

Example 1 | INTERSECT ​

sql
with
  cte_2cols1 as ( select * from  (select 'a' as ord, 10 as col1
                                  union all 
                                  select 'b' as ord,  20 as col1
                                  union all
                                  select 'c' as ord,  30 as col1
                                  union all
                                  select 'd' as ord,  40 as col1                                  union all
                                  select 'f' as ord,  NULL as col2)),
  cte_2cols2 as ( select * from  (select 'a' as ord, 10 as col2
                                  union all
                                  select 'b' as ord, 20 as col2
                                  union all
                                  select 'c' as ord, 30 as col2
                                  union all 
                                  select 'd' as ord,  40 as col2
                                  union all
                                  select 'e' as ord,  50 as col2))
select * from (select * from cte_2cols1     
intersect
select * from cte_2cols2)                   
order by ord;
;

Please also note that the result set does not contain a column 'col2'.

+-----+------+
| ORD | COL1 |
+-----+------+
| a   | 10   |
| b   | 20   |
| c   | 30   |
| d   | 40   |
+-----+------+

Example 2 | MINUS ​

sql
with
  cte_2cols1 as ( select * from  (select 'a' as ord, 10 as col1
                                  union all 
                                  select 'b' as ord,  20 as col1
                                  union all
                                  select 'c' as ord,  30 as col1
                                  union all
                                  select 'd' as ord,  40 as col1
                                  union all
                                  select 'f' as ord,  NULL as col2)),
  cte_2cols2 as ( select * from  (select 'a' as ord, 10 as col2
                                  union all
                                  select 'b' as ord, 20 as col2
                                  union all
                                  select 'c' as ord, 30 as col2
                                  union all 
                                  select 'd' as ord,  40 as col2
                                  union all
                                  select 'e' as ord,  50 as col2))
select * from (select * from cte_2cols1    
minus                                      
select * from cte_2cols2)                  
order by ord;

Please also note that the result set does not contain a column 'col2'.

+-----+------+
| ORD | COL1 |
+-----+------+
| f   | NULL |
+-----+------+

Example 3 | UNION ​

sql
with
  cte_2cols1 as ( select * from  (select 'a' as ord, 10 as col1
                                  union all 
                                  select 'b' as ord,  20 as col1
                                  union all
                                  select 'c' as ord,  30 as col1
                                  union all
                                  select 'd' as ord,  40 as col1                                  union all
                                  select 'f' as ord,  NULL as col2)),
  cte_2cols2 as ( select * from  (select 'a' as ord, 10 as col2
                                  union all
                                  select 'b' as ord, 20 as col2
                                  union all
                                  select 'c' as ord, 30 as col2
                                  union all 
                                  select 'd' as ord,  40 as col2
                                  union all
                                  select 'e' as ord,  50 as col2))
select * from cte_2cols1    
union
select * from cte_2cols2    
;

Please also note that the result set does not contain a column 'col2' and rows with 'ord' values 'a', 'b', 'c', and 'd' are not outputted twice (compare with 'Example 4 | UNION ALL').

+-----+------+
| ORD | COL1 |
+-----+------+
| a   | 10   |
| b   | 20   |
| c   | 30   |
| d   | 40   |
| f   | NULL |
| e   | 50   |
+-----+------+

Example 4 | UNION ALL ​

sql
with
  cte_2cols1 as ( select * from  (select 'a' as ord, 10 as col1
                                  union all 
                                  select 'b' as ord,  20 as col1
                                  union all
                                  select 'c' as ord,  30 as col1
                                  union all
                                  select 'd' as ord,  40 as col1                                  union all
                                  select 'f' as ord,  NULL as col2)),
  cte_2cols2 as ( select * from  (select 'a' as ord, 10 as col2
                                  union all
                                  select 'b' as ord, 20 as col2
                                  union all
                                  select 'c' as ord, 30 as col2
                                  union all 
                                  select 'd' as ord,  40 as col2
                                  union all
                                  select 'e' as ord,  50 as col2))
select * from (select * from cte_2cols1  
union all
select * from cte_2cols2)                
order by ord;
;

Please also note that the result set does not contain a column 'col2'. Rows with 'ord' values 'a', 'b', 'c', and 'd' were outputted twice.

+-----+------+
| ORD | COL1 |
+-----+------+
| a   | 10   |
| a   | 10   |
| b   | 20   |
| b   | 20   |
| c   | 30   |
| c   | 30   |
| d   | 40   |
| d   | 40   |
| e   | 50   |
| f   | NULL |
+-----+------+
© 2025 MetaKraftwerk GmbH. All Rights Reserved.