Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle NOT EXISTS

Oracle NOT EXISTS

Summary: in this tutorial, you learn how to use the Oracle NOT EXISTS operator to subtract one set of data from another.

Introduction to the Oracle NOT EXISTS operator

The NOT EXISTS operator works the opposite of the EXISTS operator. We often use the NOT EXISTS operator with a subquery to subtract one set of data from another.

Consider the following statement that uses the NOT EXISTS operator:

SELECT * FROM table_name WHERE NOT EXISTS (subquery);

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

Note that the NOT EXISTS operator returns false if the subquery returns any rows with a NULL value.

Oracle NOT EXISTS examples

See the following customers and orders tables in the sample database:

Customers and Orders tables

The following statement finds all customers who have no order:

SELECT name FROM customers WHERE NOT EXISTS ( SELECT NULL FROM orders WHERE orders.customer_id = customers.customer_id ) ORDER BY name;

Oracle NOT EXISTS example
To archive the customers who have no order, you use the following statement:

CREATE TABLE customers_archive AS SELECT * FROM customers WHERE NOT EXISTS ( SELECT NULL FROM orders WHERE orders.customer_id = customers.customer_id );

To update the credit limit of customers who have no order in 2017, you use the following UPDATE statement:

UPDATE customers SET credit_limit = 0 WHERE NOT EXISTS( SELECT NULL FROM orders WHERE orders.customer_id = customers.customer_id AND EXTRACT( YEAR FROM order_date );

And to delete all customers who had no order in 2016 and 2017 from the customers table, you use the following DELETE statement:

DELETE FROM customers WHERE NOT EXISTS( SELECT NULL FROM orders WHERE orders.customer_id = customers.customer_id AND EXTRACT( YEAR FROMorder_date ) IN( 2016, 2017 ) );

Oracle NOT EXISTS vs. NOT IN

The following statement uses the IN operator with a subquery:

SELECT * FROM table_name WHERE id IN(subquery);

Suppose the subquery 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.

id = NULL

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.

In this tutorial, you have learned how to use the Oracle NOT EXISTS operator to subtract one set of data from another.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle EXISTS
Next Oracle ANY

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.