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 IS NULL

Oracle IS NULL

Summary: in this tutorial, you will learn how to use the Oracle IS NULL and IS NOT NULL operators to check if a value in a column or an expression is NULL or not.

Introduction to the Oracle IS NULL operator

In the database world, NULL is special. It is a marker for missing information or the information is not applicable.

NULL is special in the sense that it is not a value like a number, character string, or datetime, therefore, you cannot compare it with any other values like zero (0) or an empty string (”). Generally speaking, NULL is even not equal to NULL.

Let’s see the orders table from the sample database.

orders table

The salesman_id column stores the salesman id of the salesman who is in charge of the sales order.

The following SELECT statement attempts to return all sales orders which do not have a responsible salesman:

SELECT * FROM orders WHERE salesman_id = NULL ORDER BY order_date DESC;

It returns an empty row.

The query uses the comparison operator (=) to compare the values from the salesman_id column with NULL, which is not correct.

To check if a value is NULL or not, you should use the IS NULL operator as follows:

expression | column IS NULL

The IS NULL operator returns true if the expression or column is NULL. Otherwise, it returns false.

The following query returns all sales orders that do not have a responsible salesman:

SELECT * FROM orders WHERE salesman_id IS NULL ORDER BY order_date DESC;

Here is the partial output of the query:

oracle is null operator example

Oracle IS NOT NULL operator

To negate the IS NULL operator, you use the IS NOT NULL operator as follows:

expression | column IS NOT NULL

The operator IS NOT NULL returns true if the expression or value in the column is not null. Otherwise, it returns false.

For example, the following example returns all sales orders which have a responsible salesman:

SELECT * FROM orders WHERE salesman_id IS NOT NULL ORDER BY order_date DESC;

This picture illustrates the partial output:

oralce is not null operator example

In this tutorial, you have learned how to use the Oracle IS NULL and IS NOT NULL to check if an expression or value in a column is null or not.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle LIKE
Next Oracle Joins

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.