Redshift Set Operators: UNION, EXCEPT/MINUS and INTERSECT

  • Post author:
  • Post last modified:December 18, 2019
  • Post category:Redshift
  • Reading time:4 mins read

You can use the Redshift set operators to combine similar data sets from two or more SELECT statements. Here the similar data set literally mean, the data type of the result set should also match, otherwise you have to explicitly type cast data when using Redshift set operators.

Types of Redshift Set Operators

Redshift supports the three types of set operators:

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

Redshift Set Operators Syntax

Below is the syntax of Set operators in Redshift:

query
 { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query;

Redshift UNION and UNION ALL

The UNION and UNION ALL operation combines the results of two similar sub-queries into a single result set that contains the rows that are returned by both SELECT statement. Data types of the column that you are trying to combine should match. These operations are logical OR. This operation is different from JOIN, which combine the columns from two tables.

A UNION operation removes duplicate rows from the result set; where as a UNION ALL expression does not remove duplicates.

UNION operation is usually costly compared to UNION ALL.

Redshift UNION and UNION ALL Example

Below is the example that shows how to use Redshift UNION and UNION ALL set operators.

select * from set_test1 
union 
select * from set_test2;

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


select * from set_test1 
union all
select * from set_test2;

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

Redshift INTERSECT

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

Redshift INTERSECT Examples

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

select * from set_test1 
INTERSECT
select * from set_test2;

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

Redshift EXCEPT/MINUS

The Amazon Redshift EXCEPT/MINUS operator finds the difference between the two sub-queries and return the result contains the rows from the first SELECT statement. We can use either EXCEPT or MINUS in the statements.

Redshift EXCEPT/MINUS Examples
select * from set_test1 
EXCEPT
select * from set_test2;

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

Redshift Set Operator Execution Order

INTERSECT operator takes higher precedence than the other set operations. UNION and EXCEPT/MINUS have the same precedence order. You can override the precedence ordering by using parenthesis.

Redshift Set Operator Limitations

Following are some of limitation of set operator usage:

  • If the names of the both columns in SELECT statement match, SQL displays that column name in the result. If the corresponding column names differ, SQL uses the column name from the first query in the set statement. However, you can also provide alias for columns in either of SELECT query.
  • You can specify aggregate clauses such as GROUP BY and HAVING in individual sub-queries only. You cannot use them on set operator results set.
  • You can specify an optional ORDER BY clause only in the final query in the set statement. The ordering will apply for the result set.
  • You can specify an optional LIMIT clause after the ORDER BY. LIMIT will apply to only result set.

Read:

Tags: