Write an SQL query to find these values ​​from column A in table X that are NOT present in column B of table Y

Question

Write an SQL query to find these values ​​from column A in table X that are NOT present in column B of table Y.

update: the request takes too much time (no more than 5 minutes, I did not wait for it to complete). Column B in table Y is the primary key.

update: im using oracle. The size of the table is millions (rows) for both tables. Of course I use a different WHERE clause, which means that I am comparing about 500,000 rows of table X with millions of rows in table Y

Answer 1

There are 4 ways to do this that I can think of.

• Not in (careful NULL)
• NULL ( DISTINCT)
• EXCEPT ( Oracle MINUS)

Oracle, .

Answer 2

NOT IN, any NULLS Y.B

Select A from X where not EXISTS (select * from Y where Y.B = X.A)

NOT EXISTS. OUTER JOIN , , , Y A

Answer 3

:

Select A from X where not A in (select B from Y)