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 )