NOT EXISTS and NOT IN

Oracle NOT EXISTS operator

oracle_not_exists__operator

Consider the below query

SELECT
    *
FROM
    table_name
WHERE
    NOT EXISTS (subquery);

The NOT EXISTS operator returns true if the subquery returns no row. Otherwise, it returns false.

Example

The following statement finds all customers who have no order in 2018 and 2019:

SELECT
    name
FROM
    customers
WHERE
    NOT EXISTS (
        SELECT
            NULL
        FROM
            orders
        WHERE
            orders.customer_id = customers.customer_id
            AND EXTRACT(
                YEAR FROM order_date
            ) IN(
                2018,
                2019
            )
    )
ORDER BY
    name;

NOT IN (vs) NOT EXISTS

It truly depends on the query and the data as to which is BEST.

Note that in general, NOT IN and NOT EXISTS are NOT the same!!!

SELECT COUNT(*) FROM emp WHERE empno NOT IN (SELECT mgr FROM emp); 

COUNT(*)
----------
0

Apparently there are NO rows such that an employee is not a mgr -- everyone is a mgr (or are they ?)

SELECT COUNT(*) FROM emp e1 WHERE NOT EXISTS (SELECT NULL FROM emp e2 WHERE e1.empno=e2.mgr);

COUNT(*)
----------
8

But now there are 8 people who are not managers. Beware the NULL value and NOT IN!! (also the reason why NOT IN is sometimes avoided).

NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER even -- if an "anti-join" can be used (if the sub-query is known to not return nulls)

The following statement uses the IN operator with a sub-query:

SELECT
 *
FROM
 table_name
WHERE
 id IN(subquery);
 

Suppose the sub-query returns four values 1, 2, 3, and NULL. You can rewrite the whole query above as follows:

SELECT
    *
FROM
    table_name
WHERE
    id = 1
    OR id = 2  
    OR id = 3
    OR id = NULL;
  

The following expression always returns a NULL value because a NULL value cannot compare to anything.

Because NULL means -- I don't know. (literally, null means Unknown)

So, the predicate

where x not in ( NULL )

evaluates to neither TRUE, nor FALSE

SELECT * FROM dual WHERE dummy NOT IN ( NULL ); --no rows return
SELECT * FROM dual WHERE NOT( dummy NOT IN (NULL) ); --no rows return

Therefore, the following expression returns a NULL value if any row in the result set of the subquery is NULL.

id NOT IN (subquery)

In contrast, NULL does not affect the result of the NOT EXIST operator because the NOT EXISTS operator solely checks the existence of rows in the subquery:

SELECT
    *
FROM
    table_name
WHERE
    NOT EXISTS(subquery);

In conclusion, the NOT EXISTS and NOT IN behave differently when there are null values involved.

Also see IN vs EXISTS


This article is contributed by Steve. If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.