CODEWITHSUNDEEP
sqloracle-databaseoracle11gnot-existsnotin
codeforme
codeforme

Reputation: 25

How to replace NOT IN with NOT EXISTS in this code?

I have only tried to replace NOT IN operator with NOT EXISTS in the following code. However, it is giving me an "Invalid Relational Operator error".

How can I correct this error? Am I missing something important? Please help.

SELECT DISTINCT 'v1',
                   'v2',
                   f.apples,
                   f.bananas,
                   f.oranges     
FROM fruits f
     WHERE f.code = 'F1' 
     AND f.buyer_fruit_code
     NOT EXISTS
             (SELECT c.color_code
                FROM colors c);

Upvotes: 0

Views: 3994

Answers (2)

John Bollinger
John Bollinger

Reputation: 181114

The NOT IN operator means something different from a NOT EXISTS clause. Among other things, NOT EXISTS is not an operator. Perhaps you want something like this:

SELECT DISTINCT 'v1',
               'v2',
               f.apples,
               f.bananas,
               f.oranges     
FROM fruits f
WHERE
  f.code = 'F1' 
  AND NOT EXISTS (
    SELECT c.color_code
    FROM colors c
    WHERE c.color_code = f.buyer_fruit_code
  )
;

Upvotes: 2

Sparky
Sparky

Reputation: 15105

Try this instead:

SELECT DISTINCT 'v1',
                       'v2',
                       f.apples,
                       f.bananas,
                       f.oranges     
    FROM fruits f
    LEFT JOIN color_code c ON f.fruit_code=c.color_code
         WHERE f.code = 'F1' 
         AND c.color_code is NULL

Upvotes: 0

Related Questions