Good afternoon,
I would like to know what the difference of these operators ... are the same? Return the same result? If possible I would like an example, I already read some texts on the internet ... but not I was able to understand their differences very well. Thanks.
In / Exists and Not In / Not Exists
-
- Moderador
- Posts: 641
- Joined: Mon, 03 Sep 2007 3:26 pm
- Location: Fortaleza - CE
att,
Daniel N.N.
Daniel N.N.
In general, they return the same result. One is not necessarily better than the other in terms of performance, it will depend on how your data is, and your query.
However, "not in" and "Not Exists" can return different data when used fields that can be null.
Picking up a ready-made tone:
Apparently, all overwhelmed is manager. However:
Already with "Not Exists" we note that there are 9 people who are not managers. In the "not in" he ignores the null.
Another thing, the return fields within an existations does not matter. It only checks if the query "wheel" or not.
However, "not in" and "Not Exists" can return different data when used fields that can be null.
Picking up a ready-made tone:
SQL> select count(*) from emp where empno not in ( select mgr from emp );
COUNT(*)
----------
0
SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );
COUNT(*)
----------
9
Another thing, the return fields within an existations does not matter. It only checks if the query "wheel" or not.
- fbifabio
- Moderador
- Posts: 199
- Joined: Tue, 22 Feb 2011 1:51 pm
- Location: São Paulo - SP
- Contact:
Fábio Prado
www.fabioprado.net
www.fabioprado.net
Personnel,
according to the "Oracle Database Performance Tuning Guide" in certain circumstances is best to use in than existing and vice versa.
In general, in OLTP environments, if the selective predicate is in the subquery use in. If the selective predicate is on the query parent, use exist.
More details Explain and demonstrate in My SQL Tuning Training: http://www.fabioprado.net/p/sql-tuning- ... racle.html
[] S
Fábio Prado www.fabioprado.net]]
according to the "Oracle Database Performance Tuning Guide" in certain circumstances is best to use in than existing and vice versa.
In general, in OLTP environments, if the selective predicate is in the subquery use in. If the selective predicate is on the query parent, use exist.
More details Explain and demonstrate in My SQL Tuning Training: http://www.fabioprado.net/p/sql-tuning- ... racle.html
[] S
Fábio Prado www.fabioprado.net]]
-
- Information
-
Who is online
Users browsing this forum: No registered users and 3 guests