amazon-web-servicesamazon-athenaset-operations

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',

Solution

  • 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;