OraFAQ Forum: Server Administration » alternate SQL without Exists clause

Home » RDBMS Server » Server Administration » alternate SQL without Exists clause

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

alternate SQL without Exists clause [message #371095]

Fri, 25 August 2000 04:26

suriyan
Messages: 3
Registered: August 2000

Junior Member

select table1.field1
from table1
where NOT EXISTS (select table2.field1
from table2
where table2.field1 = table1.field1)

can anyone suggest an alternative..without Using IN/EXISTS Clauses, preferably without using SubQueries also.

Thanks

Report message to a moderator

Re: alternate SQL without Exists clause [message #371102 is a reply to message #371095]

Fri, 25 August 2000 15:17

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

by definition, you are running a Correlated Subquery which you have probably found out is slow.

Take a look at the MINUS operation, it's especially good for eliminating "not in" which is very similar to your "not exists". MIUNS is fast!

Report message to a moderator

Re: alternate SQL without Exists clause [message #371106 is a reply to message #371095]

Mon, 28 August 2000 00:38

suriyan
Messages: 3
Registered: August 2000

Junior Member

i am trying to execute this SQL via an ODBC driver, the feature of this driver is very limited, i cannot write any Correlated Subqueries.

i need to rewrite this SQL, without any subqueries, is there anyother ALTERNATIVE Logic..

Report message to a moderator

Re: alternate SQL without Exists clause - try me [message #371108 is a reply to message #371095]

Mon, 28 August 2000 12:02

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

Try this one, you may have to play with the code a bit.

SELECT table1.field1
FROM table1, table2
WHERE table1.field1 = table2.field1 (+)
AND table2.field1 IS NULL

Report message to a moderator