Handling NULL value in SQL
In this article we will quickly look how to handle the NULL value in an SQL table. A field with a NULL value is a field with no value. The NULL value created when the field has been left blank during record creation!
To prevent null insertion we can declare the constraints on our table row. NOT NULL constraints solves null values:
CREATE TABLE EMPLOYEES ( EMP_ID NUMBER(10) NOT NULL, EMP_NAME VARCHAR2(50), DESIGNATION VARCHAR2(50) );
If we try to insert NULL to the EMP_ID we will get the below error,
INSERT INTO EMPLOYEES VALUES (NULL, 'Sai Shree', 'Project Manager')
Error report -
ORA-01400: cannot insert NULL into ("KASELOCAL"."EMPLOYEES"."EMP_ID")Demo Table
Below is a selection from the “EMPLOYEES” table
| EMP_ID | EMP_NAME | DESIGNATION |
| 1 | Harry | HR |
| 2 | John | (null) |
| 3 | Taqueer | Developer |
| 4 | Akilesh | (null) |
| 5 | Sai Shree | Project Manager |
Check for NULL value
We can use IS NULL and IS NOT NULL operator to know is here any NULL values in table.
Syntax
SELECT column_names FROM table_name WHERE column_name IS NULL || IS NOT NULL;
Example for IS NULL
SELECT * FROM EMPLOYEES WHERE DESIGNATION IS NULL;
Output
EMP_ID | EMP_NAME | DESIGNATION -------+--------------+-------------- 2 | John | (null) 4 | Akilesh | (null)
Example for IS NOT NULL
SELECT * FROM EMPLOYEES WHERE DESIGNATION IS NOT NULL;
Output
EMP_ID | EMP_NAME | DESIGNATION -------+--------------+-------------- 1 | Harry | HR 2 | Taqueer | Developer 3 | Sai Shree | Project Manager
Happy Coding 🙂
