What is the difference between Not In and Not Exists in Snowflake?

NOT IN does not have the ability to compare the NULL values. Not Exists is recommended in such cases. When using “NOT IN”, the query performs nested full table scans. Whereas for “NOT EXISTS”, the query can use an index within the sub-query.

[ NOT ] EXISTS
An EXISTS subquery is a boolean expression that can appear in a WHERE or HAVING clause, or in any function that operates on a boolean expression. An EXISTS expression evaluates to TRUE if any rows are produced by the subquery. A NOT EXISTS expression evaluates to TRUE if no rows are produced by the subquery.

Correlated EXISTS subqueries are currently supported only in a WHERE clause.
Correlated EXISTS subqueries cannot appear as an argument to an OR operator.
Uncorrelated EXISTS subqueries are supported anywhere that a boolean expression is allowed.

Examples
Use a correlated NOT EXISTS subquery to find the departments that have no employees:

SELECT department_id
FROM departments d
WHERE NOT EXISTS (SELECT 1
FROM employees e
WHERE e.department_id = d.department_id);

[ NOT ] IN
The IN and NOT IN operators check if an expression is included or not included in the values returned by a subquery.

IN is shorthand for = ANY, and is subject to the same restrictions as ANY subqueries.
NOT IN is shorthand for != ALL, and is subject to the same restrictions as ALL subqueries.
[NOT] IN can also be used as an operator in expressions that do not involve a subquery. For details, see [ NOT ] IN.

Examples
Use a NOT IN subquery that is equivalent to the != ALL subquery example (earlier in this topic):

SELECT department_id
FROM departments d
WHERE d.department_id NOT IN (SELECT e.department_id
FROM employees e);

I have used these approaches in my project, Please try to implement them in your project and let me know if you have any concerns.

Thanks for taking the time to read this blog. Let me know if you have any questions or comments!