Solved

Exists versus In

Posted on 2014-02-19
10
263 Views
Last Modified: 2014-02-24
This was an interview question asked:

Is there any scenario where exists would be used but not  IN.
 
My answer: Exists returns boolean value and IN returns a value and exists is faster performance-wise. They donot want to know about the performance but they wanted to know a scenario where only Exists could be used but not IN
0
Comment
Question by:d27m11y
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39871753
>>and exists is faster performance-wise

Tom Kyte explains why this may not be true:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:953229842074

>>wanted to know a scenario where only Exists could be used but not IN

Of the top of my head I cannot think of one.  I'm sure some other Expert will have one if I cannot think of one later.
0
 
LVL 23

Expert Comment

by:David
ID: 39871887
Prior to 10g, EXISTS was preferred for a small result set.  The CBO has long since been improved.
0
 

Author Comment

by:d27m11y
ID: 39872026
How about NULL values, am not sure.. can anyone shed light ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39872067
>>How about NULL value

There is the example I couldn't think of.

IN lists cannot process nulls where EXISTS can.

Take the following test case:
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','a');
insert into tab1 values('1',null);
commit;

select t1.col1 from tab1 t1 where exists (
	select null from dual
);

select t1.col1 from tab1 t1 where col2 in (
	select null from dual
);

Open in new window

0
 
LVL 8

Expert Comment

by:Surrano
ID: 39872784
@slightwv I haven't tested this but lines 8-10 don't look like making any sense. It will return all rows since select anything from dual will always be a non-empty resultset i.e. exists evaluates to constant true.

I believe exists makes sense on a relatively small table if you can use a good where condition e.g. using an index of a huge table:

select * from smalltable where exists 
(select null from hugetable where hugetable.indexedfield = smalltable.somefield);

select * from smalltable where somefield in (select indexedfield from hugetable);

Open in new window


As far as I can tell Oracle (as well as MySQL or PostgreSQL) would do something like:
- for IN: perform full table scan on hugetable and join the resultset with smalltable
- for EXISTS: perform full table scan on smalltable and a relatively small number of index searches on hugetable.

But I can imagine that one or more of the above systems, also possibly depending on table statistics, software version and perf tuning config, would rewrite the IN query to EXISTS or vica versa.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:David
ID: 39873353
To the interview question, then, your answer seems to be "no".  Disregarding performance as directed, IN does not currently have any declarative restriction.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39874185
I think there might be cases when exists can be used but not in. Something like the following where you want to get an audit report but only where there is an error in the error_log table for today:

select * from audit_table where exists
(select 1 from error_log where errdate > trunc(sysdate));
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39876239
This can also be rewritten as "IN":

select * from audit_table where 1 in
(select 1 from error_log where errdate > trunc(sysdate)); 

Open in new window


And this is actually a proof of concept: the following two boolean expressions are equal:

exists (select 1 from ...) 
1 in (select 1 from...)

Open in new window

Note that you can replace 1 with any non-null constant of any type.

There is a nuance in the original question though; "where exists would be used but not  IN." The concept above is not about "would" more like "could". Just in case it matters.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39876768
Surrano, good point. I didn't think about the use of a literal value in a select of that literal value.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39877250
>>is not about "would" more like "could". Just in case it matters.

Even though you discount my over-simplified test case as not making any sense.  I stand by my comment.

When nulls are involved, EXISTS will work an IN will not.  I can use EXISTS to look for nulls and I cannot rewrite it to use a null in an IN list.

I cannot say:   ... where column in (a,b,c,null)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL/SQL - Leading zeros 7 59
Oracle - Query Insert and Update multiple tables 5 57
VB.Net - CSV to Oracle table 4 52
Bash Script to Analyze Oracle Schemas 11 84
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now