Vertica Set Operators: UNION, EXCEPT/MINUS and INTERSECT

  • Post author:
  • Post last modified:April 26, 2019
  • Post category:Vertica
  • Reading time:6 mins read

The Vertica set operators are used to combine similar data sets from two or more SELECT statements. Here the similar data set literally means, the data type of the result set should also match, otherwise you have to explicitly use type conversion function for data columns when using set operators.

The set operators in SQL are extensively used to combine results from various query sets. Based on your requirement, you can choose suitable Vertica set operators.

Related Articles:

Types of Vertica Set Operators

Just like any other relational databases, Vertica supports three set operators:

  • UNION [DISTINCT] and UNION ALL
  • INTERSECT [DISTINCT]
  • EXCEPT [DISTINCT] or MINUS [DISTINCT]

Vertica Set Operators Syntax

Below is the syntax of Set operators in HPE Vertica:

query
 { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query
[Order by [cols]]
[limit];

Vertica UNION and UNION ALL

The UNION and UNION ALL operation in Vertica combines the results of two similar SELECT statements or sub-queries into a single result set that contains the rows from both SELECT statements. You can include UNION in FROM, WHERE, and HAVING clauses.

The two-data sets should be similar. i.e. data types of the column that you are trying to combine should match. You can consider UNION or UNION ALL operations as a logical OR. This operation is different from JOIN, which combine the columns from two or more tables based on common column such as primary and foreign keys.

The SQL UNION operation usually removes duplicate rows from the result set; where as a UNION ALL expression does not remove duplicates, hence, UNION operation is usually costly compared to UNION ALL.

Vertica UNION and UNION ALL Examples

Below examples demonstrate on usage of Vertica set operators:

select * from test1 
union 
select * from test2;

id | name 
----+------ 
 1 | ABC 
 2 | BCD 
 5 | DEF 
 6 | EFG 
 3 | CDE 
 7 | FGH 
(6 rows) 


select * from test1 
union all
select * from test2;

id | name 
----+------ 
 2 | BCD 
 5 | DEF 
 6 | EFG 
 3 | CDE 
 1 | ABC 
 3 | CDE 
 2 | BCD 
 7 | FGH 
(8 rows)

Vertica INTERSECT Operator

The Vertica INTERSECT operator combines the similar results of two sub-queries into a single result set that contains rows common to multiple SELECT statements. Data types of the columns that you are trying to combine should match.

Vertica INTERSECT Operator Examples

Below is the example that shows how to use Vertica INTERSECT operator:

select * from test1 
INTERSECT
select * from test2;

id | name 
----+------ 
 2 | BCD 
 3 | CDE 
(2 rows)

Vertica EXCEPT/MINUS Operator

The Vertica EXCEPT/MINUS operator finds the difference between the two sub-queries and return the result contains the rows from the first or LEFT SELECT statement. The EXCEPT is alias for MINUS operator.

Vertica EXCEPT/MINUS Operator Examples

Below example demonstrates EXCEPT operator in Vertica:

select * from test1 
EXCEPT
select * from test2;

id | name 
----+------ 
 1 | ABC 
 5 | DEF 
(2 rows)

Vertica Set Operator Execution Order

The INTERSECT operator takes higher precedence than the other set operations. The UNION and EXCEPT/MINUS have the same precedence order.

You can override the precedence ordering by using parenthesis.

Vertica Set Operator Limitations

Following are some of limitation of set operator usage:

  • You cannot use the ALL keyword with a UNION, UNION ALL, INTERSECT and EXCEPT query.
  • Each SELECT statements must specify same number of columns.
  • If the names of the both columns in the SELECT statements match, the result set will have that column name. If the corresponding column names differ, the result set will have column names from the first or left query in the set statement. However, you can also provide alias for columns in either of SELECT queries.
  • You can specify an optional ORDER BY clause only in the final query of the set statement. The ordering will apply for the result set.
  • You can specify an optional LIMIT clause after the ORDER BY. The LIMIT will apply to only result set.

Hope this helps 🙂

Tags: ,