Home » SQL & PL/SQL » SQL & PL/SQL » how to change the query to Not exists from NOT IN
how to change the query to Not exists from NOT IN [message #19979] Mon, 22 April 2002 11:09 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
I have a query like this, i want to change the query from NOT IN to
NOT EXISTS. How do i do it.

SELECT fichannel
FROM channel
WHERE finews = 1000
and fichannel not in
(SELECT DISTINCT finotificationchannel
FROM msgtype )
where msgtype.fichannel = channel.fichannel
and channel.finews=1581)

Report message to a moderator

Re: how to change the query to Not exists from NOT IN [message #19993 is a reply to message #19979] Tue, 23 April 2002 04:05 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
SELECT fichannel
FROM channel a
WHERE finews = 1000
and fichannel not exists
(SELECT DISTINCT finotificationchannel
FROM msgtype b where b.fichannel = a.fichannel
and channel.finews=1581)

Report message to a moderator

Re: how to change the query to Not exists from NOT IN [message #20004 is a reply to message #19993] Wed, 24 April 2002 00:54 Go to previous message
John R
Messages: 156
Registered: March 2000
Senior Member
A more syntactically accuate and efficient NOT EXISTS would look like this:

SELECT fichannel
FROM channel a
WHERE finews = 1000
and not exists
(SELECT 1
FROM msgtype )
FROM msgtype b where b.fichannel = a.fichannel
and channel.finews=1581)

Report message to a moderator

Previous Topic: A good Question on stored proc
Next Topic: Re: How to load ....
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 05:23:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.