I have first table with millions of rows with multiple columns, example:
ID Name Dept City State
11 sam sales Boston MA
22 Bob market Atlanta GA
25 Mike IT SF CA
and 2nd lookup table contains two rows with Number and States, example is as follows,
No State
1 CA
2 TX
How can I get results from first table excluding rows with state value present in table 2?
Spark does not support subqueries, How this can be done in spark?
SELECT * FROM table1
WHERE state NOT IN (SELECT state FROM table2 )
This is generic SQL, you can tweak it to your specific database.
On a separate note, if you have millions of records, make sure that the fields you are filtering on have indexes on them.
let me know if this helps.
select * from firsttable
where State not in (select distinct state from secondtable)
try this :
SELECT *
FROM Table1
LEFT JOIN Table2
ON (Table1.state = Table2.state)
WHERE Table2.state IS NULL
