Unable to execute nested SQL queries in Spark SQL

Question

I am trying to execute this query but it doesn't work:

SELECT COLUMN
FROM TABLE A           
WHERE  A.COLUM_1 = '9999-12-31' AND NOT EXISTS (SELECT 1 FROM TABLE2 ET WHERE ET.COl1 = A.COL2 LIMIT 1)

It results in an error which says the following:

"mismatched input FROM expecting"

Gordon Linoff · Accepted Answer · 2018-09-12 11:38:36Z

4

I'm not sure that SparkSQL supports TOP. But it is not needed. Does this work?

SELECT t.COLUMN
FROM TABLE t           
WHERE t.COLUM_1 = '9999-12-31' AND
      NOT EXISTS (SELECT 1 FROM TABLE2 ET WHERE ET.COl1 = t.COL2);

This fixes a few other syntax issues with the query (such as no alias A).

LIMIT in the subquery is also not needed. NOT EXISTS should stop at the first match.

answered Sep 12, 2018 at 10:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yes iam sorry TOP is incorrect as its not supported, i should be using LIMIT 1 i suppose instead of TOP 1, Thanks BTW