Menu
What's the difference between not in and not exists in an Oracle query?
When do I use not in? And not exist?
673
The NULL is considered and returned by the NOT IN command as a value. The SQL NOT EXISTS command is used to check for the existence of specific values in the provided subquery. The subquery will not return any data; it returns TRUE or FALSE values depend on the subquery values existence check.
IN operator always picks the matching values list, whereas EXISTS returns the Boolean values TRUE or FALSE. EXISTS operator can only be used with subqueries, whereas we can use the IN operator on subqueries and values both.
Where NOT EXISTS SQL is used? Ans:- NOT EXISTS SQL means nothing returned by the subquery. It is used to restrict the number of rows returned by the SELECT statement. In the server, it checks the Subquery for row existence, and if there are no browns then it will return true otherwise false.
The EXISTS clause is faster than IN when the subquery results are very large. The IN clause is faster than EXISTS when the subquery results are very small.
The difference between NOT IN and NOT EXISTS becomes clear where there are NULL values included in the result.
For example:
create table test_a (col1 varchar2(30 char));
create table test_b (col1 varchar2(30 char));
insert into test_a (col1) values ('a');
insert into test_a (col1) values ('b');
insert into test_a (col1) values ('c');
insert into test_a (col1) values ('d');
insert into test_a (col1) values ('e');
insert into test_b (col1) values ('a');
insert into test_b (col1) values ('b');
insert into test_b (col1) values ('c');
insert into test_b (col1) values (null);
Note: They key difference is that test_b contains a null value.
select * from test_a where col1 not in (select col1 from test_b);
No rows returned
select * from test_a where
not exists
(select 1 from test_b where test_b.col1 = test_a.col1);
Returns
col1
====
d
e
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
