SQL - SPLessons

SQL NULL Values

Chapter 27

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL NULL Values

SQL NULL Values

shape Description

NULL qualities are generally denoted as blank. If any value is not able to insert to a column of a table then SQL automatically assign a NULL esteems to that column if the column is not a Not Null segments or Primary Key. The IS NULL condition is used to select records with NULL esteems in a segment. And IS NOT NULL condition is used to select records which does not have any NULL values in any columns and can be used in Insert, Delete, Select and Update statement.

shape Conceptual
figure

shape Syntax

The syntax for Not NULL operator is as follows.
Expression WHERE column_name IS [NOT] NULL;

shape Examples

The below example illustrates the ISNULL condition in sql database.

sql> create table employee(emp_id number(4),ename varchar2(20),salary number);
Query OK, 0 rows affected (0.63 sec)

sql> insert into employee values(1001,'jack',12000);
Query OK, 1 row affected (0.10 sec)

sql> insert into employee values(1002,'mack',13000);
Query OK, 1 row affected (0.39 sec)

sql> insert into employee values(1003,'james',14000);
Query OK, 1 row affected (0.39 sec)

sql> insert into employee values(1004,'kate',Null);
Query OK, 1 row affected (0.39 sec)

sql> select * from employee;
+--------+-------+-------+
| emp_id | ename | salary|
+--------+-------+-------+
|   1001 | jack  | 12000 |
|   1002 | mack  | 13000 |
|   1003 | james | 14000 |
|   1004 | Kate  | Null  |
+--------+-------+-------+
4 rows in set (0.00 sec)

sql> select  * from employee where sal IS NULL;
+--------+-------+-------+
| emp_id | ename | sal   |
+--------+-------+-------+
|   1004 | Kate  | Null  |
+--------+-------+-------+
1 rows in set (0.00 sec)

sql> delete from employee where salary IS NULL;
+--------+-------+-------+
| emp_id | ename | sal   |
+--------+-------+-------+
|   1001 | jack  | 12000 |
|   1002 | mack  | 13000 |
|   1003 | james | 14000 |
+--------+-------+-------+
3 rows in set (0.00 sec)

Summary

shape Key Points

  • SQL NULL Values – The NULL Value condition is used to select records with NULL values in a column.