CODEWITHSUNDEEP
amazon-web-servicesamazon-athenaset-operations
mon
mon

Reputation: 22244

Does AWS Athena support set operation?

Try to use set operation but seems not to work in Athena. Is it not supported or is there anything wrong with the SQL?

SELECT DISTINCT cik FROM xbrl
MINUS
SELECT cik FROM xbrl
WHERE year IN (2015,2014,2013,2012,2011,2010)

line 3:1: mismatched input 'SELECT'. Expecting: '(', ',', 'CROSS', 'EXCEPT', 'FULL', 'GROUP', 'HAVING', 'INNER', 'INTERSECT', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'OFFSET', 'ORDER', 'RIGHT', 'TABLESAMPLE', 'UNION', 'WHERE',

Upvotes: 1

Views: 2110

Answers (2)

Sushma Pachpute
Sushma Pachpute

Reputation: 1

for set operation in Athena you can use { UNION | INTERSECT | EXCEPT }

SELECT DISTINCT cik FROM xbrl EXCEPT SELECT cik FROM xbrl WHERE year IN (2015,2014,2013,2012,2011,2010)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

It appears that Athena doesn't support MINUS, but usually we can express a minus query in other ways. In this case, use:

SELECT DISTINCT cik
FROM xbrl
WHERE year < 2010 OR year > 2015;

Upvotes: 1

Related Questions