NULL Values And Is Null or Is Not Null in Oracle SQL | Oracle SQL Tutorials -9

Hi,

I will explain NULL Values And Is Null or Is Not Null in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

Insert Into Select Statement in Oracle SQL | Oracle SQL Tutorials -8

THE NULL VALUE

• Null is a value that is unknown, unspecified or inconvenient.
• Null value is not zero(0) or whitespace. It is a common mistake.
• If a null value joins an arithmetic operation the result will be null.

For example; There are lots of Null values in the COMMISSION_PCT column of hr.employees table as follows.

SQL> select first_name,last_name,salary,commission_pct from hr.employees where salary>12000;

FIRST_NAME           LAST_NAME                     SALARY COMMISSION_PCT
-------------------- ------------------------- ---------- --------------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
Nancy                Greenberg                      12008
John                 Russell                        14000             .4
Karen                Partners                       13500             .3
Michael              Hartstein                      13000
Shelley              Higgins                        12008

8 rows selected.

You cannot use Null values with comparison operators, such as =, <, or <>.

There are two types of operators to test if any column is NULL or not as follows.

• IS NULL
• IS NOT NULL

IS NULL Syntax

You can check and test if any column data is NULL as follows.

SELECT * FROM table_name WHERE column_name IS NULL;

You can check all employess that salary is greater than 12000 and COMMISSION_PCT is NULL as follows.

SQL> select first_name,last_name,salary,commission_pct from hr.employees where salary>12000 and COMMISSION_PCT IS NULL;

FIRST_NAME           LAST_NAME                     SALARY COMMISSION_PCT
-------------------- ------------------------- ---------- --------------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
Nancy                Greenberg                      12008
Michael              Hartstein                      13000
Shelley              Higgins                        12008

6 rows selected.

SQL>

IS NOT NULL Syntax

You can check and test if any column data IS NOT NULL as follows.

SELECT* FROM table_name WHERE column_name IS NOT NULL;

SQL> select first_name,last_name,salary,commission_pct from hr.employees where salary<9000 and COMMISSION_PCT IS NOT NULL;

FIRST_NAME           LAST_NAME                     SALARY COMMISSION_PCT
-------------------- ------------------------- ---------- --------------
Christopher          Olsen                           8000             .2
Nanette              Cambrault                       7500             .2
Oliver               Tuvault                         7000            .15
Lindsey              Smith                           8000             .3
Louise               Doran                           7500             .3
Sarath               Sewall                          7000            .25
Mattea               Marvins                         7200             .1
David                Lee                             6800             .1
Sundar               Ande                            6400             .1
Amit                 Banda                           6200             .1
William              Smith                           7400            .15

FIRST_NAME           LAST_NAME                     SALARY COMMISSION_PCT
-------------------- ------------------------- ---------- --------------
Elizabeth            Bates                           7300            .15
Sundita              Kumar                           6100             .1
Alyssa               Hutton                          8800            .25
Jonathon             Taylor                          8600             .2
Jack                 Livingston                      8400             .2
Kimberely            Grant                           7000            .15
Charles              Johnson                         6200             .1

18 rows selected.

SQL>

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )