Search code examples
sqlhiveimpala

How to use MINUS operator in Impala/Hive?

How can I use a MINUS operator query in Impala?

Example:

Table: Game

Columns: Official_start, halftime_start

I want to send a query in which halftime_start - official_start and save it as firsttimeplayed

I know that MINUS does not exist in Impala or HIVE but how can I do it without it?

Solution

  • MINUS is a set-operation in some versions of SQL. It operates on sets and in this way is similar to UNION and UNION ALL. It takes the rows from the first query that do not exist in the second. I believe the ANSI standard for the operator is EXCEPT rather than MINUS. Hive/Impala support neither MINUS nor EXCEPT.

    - is the subtraction operator. That appears to be what you want; it is the inverse operation of addition or +. Unfortunately, another name for subtraction in English is "minus", leading to your confusion.

    So, you just do:

    select (halftime_start - official_start) as firsttimeplayed
. . .