Difference between "EXISTS" and "IN" operators in terms of performance in SQL...

EXISTS is much faster than IN, when the sub-query results are very large, the EXISTS operator provides better performance, but IN is faster than EXISTS, when the sub-query results are very small.

The Exists keyword evaluates true or false, but the IN keyword compares all value in the corresponding subquery column. Another one Select 1 can be used with Exists command. When a subquery returns a NULL value then the whole statement becomes NULL. In those cases we are using the EXITS keyword.

If there are 1000 rows in table2 and 1000 rows in table1, 1,000,000 logical rows will be visited to answer the simple query. If there are a million rows in each, 100 trillion rows (10^14) will be visited. So, please use NOT EXISTS - your production DBAs will be grateful.

Update: another “teaching moment” aspect of the above is the NOT EXISTS form looks “more complicated” than the NOT IN form. But in the 1M rows in each table case - and if there’s an index on table2. Value - the NOT EXISTS form would only visit 1M * (ln 1M) or about 13.8M logical rows, which actually has a chance of returning in a reasonable time (a couple of seconds or less on most modern DB servers). The NOT IN form will take about 7.2M times longer…