Search code examples
sqlsubquerywhere-clauseimpala

"WHERE" clause with subqueries in "IN"

I'm trying (on Impala SQL) to get the rows that have the biggest/ smallest difference between two columns, and I'm trying something like this:

SELECT * 
FROM table 
WHERE col1 - col2 IN ( SELECT MAX(col1-col2) 
                       FROM table, SELECT MIN(col1-col2) FROM table )

Using only one subquery works, but if I add both of them inside IN it gives an error.

Any suggestions on how I can do this?

Solution

  • Use a subquery join:

    SELECT * 
FROM table t
JOIN (
  SELECT MIN(col1 - col2) AS min_diff, MAX(col1 - col2) AS max_diff
  FROM table
) AS agg ON t.col1 - t.col2 IN (agg.min_diff, agg.max_diff)