OraFAQ Forum: SQL & PL/SQL » Why the second query is more efficient?

Home » SQL & PL/SQL » SQL & PL/SQL » Why the second query is more efficient?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why the second query is more efficient? [message #598277]

Sat, 12 October 2013 02:17

zhaoquer
Messages: 35
Registered: October 2013

Member

select .........
from TAB1
where COL1 like 'ABC%'
and COL2 NOT IN
(select FLD2 from TAB2 where FLD3 between '20130101' and '20130131');

select ........
from TAB1
where COL1 like 'ABC%'
and COL2 NOT EXISTS
(select FLD2
from TAB2
where FLD2 = COL2
AND FLD3 between '20130101' and '20130131');

Report message to a moderator

Re: Why the second query is more efficient? [message #598280 is a reply to message #598277]

Sat, 12 October 2013 02:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is not.

Report message to a moderator

Re: Why the second query is more efficient? [message #598282 is a reply to message #598280]

Sat, 12 October 2013 02:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In addition, they are not equivalent:

create table t (col varchar2(1));
insert into t values ('Y');
insert into t values (null);
commit;

SQL> select * from dual;
D
-
X

1 row selected.

SQL> select * from t;
C
-
Y


2 rows selected.

SQL> select * from dual a where dummy not in (select col from t);

no rows selected

SQL> select * from dual where not exists (select null from t where col=dummy);
D
-
X

1 row selected.

Report message to a moderator

Re: Why the second query is more efficient? [message #598285 is a reply to message #598282]

Sat, 12 October 2013 03:02

zhaoquer
Messages: 35
Registered: October 2013

Member

Thanks Michel!
When i delete the null row in table t;
select * from dual a where dummy not in (select col from t);
D
-
X

1 row selected.
why it happened?

Report message to a moderator

Re: Why the second query is more efficient? [message #598288 is a reply to message #598285]

Sat, 12 October 2013 03:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is basic of SQL but often a misunderstood point.
dummy NOT IN (select ...)
means
dummy NOT IN (all the values returned by the query)
that is here
NOT IN ('Y',null)
which means
dummy <> 'Y' AND dummy <> null
this condition is TRUE (which is the condition for the row to be returned) only if the 2 sides of AND are TRUE but the second part with NULL can NEVER be TRUE, it is always UNKNOWN.
So the whole condition is always UNKNOWN (whatever the value of dummy column) and not TRUE and no rows are returned.

Database SQL Reference

[Updated on: Sat, 12 October 2013 05:17]

Report message to a moderator

Re: Why the second query is more efficient? [message #598289 is a reply to message #598288]

Sat, 12 October 2013 03:51

zhaoquer
Messages: 35
Registered: October 2013

Member

Thanks again!

Report message to a moderator

Re: Why the second query is more efficient? [message #598294 is a reply to message #598289]

Sat, 12 October 2013 05:18

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So in summary, if the subquery of a NOT IN returns at least one NULL then the query returns no row.

Report message to a moderator

Re: Why the second query is more efficient? [message #598295 is a reply to message #598277]

Sat, 12 October 2013 07:02

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

zhaoquer wrote on Sat, 12 October 2013 12:47

(select FLD2 from TAB2 where FLD3 between '20130101' and '20130131');

I am sure FLD3 is a DATE column. What is the data type, DATE or VARCHAR?

In your query, you will always be safe if you write :

 Select FLD2 from TAB2 where FLD3 between to_date('20130101' , 'YYYYMMDD') and to_date('20130131' , 'YYYYMMDD')

Regards,
Lalit

Report message to a moderator

Re: Why the second query is more efficient? [message #598299 is a reply to message #598295]

Sat, 12 October 2013 08:18

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Some of professional illnesses like carpal tunnel and arthritis could be avoided if we respect our hands and fingers, so use date literals Laughing

select FLD2 from TAB2 where FLD3 between date '2013-01-01' and date '2013-01-31'
/

SY.

Report message to a moderator

Re: Why the second query is more efficient? [message #598302 is a reply to message #598299]

Sat, 12 October 2013 10:40

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

TIMESTAMP literal contains time portion.

[Updated on: Thu, 06 March 2014 03:12] by Moderator

Report message to a moderator

Previous Topic:	Remove string duplicates
Next Topic:	dynamic cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:36:35 CDT 2024