SQL NULL Values (empty value)

What is the SQL NULL value?

---

In SQL, NULL is used to represent missing values. The NULL value in the data table indicates that the field in which the value is located is empty.

A field with a NULL value is a field with no value.

If a field in a table is optional, you can insert a new record or update the record without adding a value to the field. The field is then saved as a NULL value.

A field with a value of NULL has no value. In particular, the NULL value is different from a 0 or field that contains blank spaces.

Note: It is important to understand that NULL values are different from zero values or fields that contain spaces. A field with a NULL value is one that is left blank during record creation!

How do I test NULL values?

---

It is not feasible to test the NULL value using a comparison operator ( e.g., .

We will have to use the IS NULL and IS NOT NULL operators.

IS NULL syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Demonstrate the database

---

Suppose we have the following People table:

Id	LastName	FirstName	Address	City
1	Doe	John	542 W. 27th Street	New York
2	Bloggs	Joe		London
3	Roe	Jane		New York
4	Smith	John	110 Bishopsgate	London

Suppose the Address column in the People table is optional. If the inserted record does not have an Address value, the Address column is saved at an empty value.

IS NULL operator

---

The following SQL statement uses the IS NULL operator to list all people without addresses:

SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NULL;

The result set will look like this:

LastName	FirstName	Address
Bloggs	Joe
Roe	Jane

Tip: Always use IS NULL to find empty values.

IS NOT NULL operator

---

The following SQL statement uses the IS NOT NULL operator to list all people with addresses:

SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NOT NULL;

The result set will look like this:

LastName	FirstName	Address
Doe	John	542 W. 27th Street
Smith	John	110 Bishopsgate

Grammar:

---

When you create a table, the basic syntax of NULL is as follows:

SQL> CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Here, NOT NULL means that a given column must be explicitly assigned according to its data type. Two columns are not qualified with NOT NULL, which means that they can be NULL.

A field with a value of NULL is one that is left blank during record creation.

Example:

---

NULL values can cause trouble in picking up data. However, because NULL is compared to any other value, the result is always unknown, so records containing NULL are not included in the final result.

Either IS NULL or IS NOT NULL must be used to detect whether a field is NULL.

Consider the following CUSTOMERS data table, which contains the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+

Here's how the IS NOT NULL operator is used:

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
     FROM CUSTOMERS
     WHERE SALARY IS NOT NULL;

The above statement runs as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
+----+----------+-----+-----------+----------+

Here's how the IS NULL operator is used:

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
     FROM CUSTOMERS
     WHERE SALARY IS NULL;

The results are as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  6 | Komal    |  22 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+