NULL VALUES

The SQL NULL is the term used to represent a missing value. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Syntax:

The basic syntax of IS NULL :

SELECT column-names

  FROM table-name

 WHERE column-name IS NULL

The basic syntax of NOT NULL :

SELECT column-names

  FROM table-name

  WHERE column-name IS NOT NULL

Example:

The NULL value can cause problems when selecting data, however, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results.You must use the IS NULL or IS NOT NULL operators in order to check for a NULL value.

Here is the EMPLOYEE1 table:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	12,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	15,000	BHUBANESWAR
7	G	01/01/2016 00:00:00	07/06/2016 00:00:00	11,000	PUNE
8	A	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	GOA
8	B	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	DELHI
9	H	01/01/2016 00:00:00	07/06/2016 00:00:00		HYDERABAD
10	I	01/01/2016 00:00:00	07/06/2016 00:00:00		KILKATA

1) Now, following is the usage of NOT NULL operator:

SELECT  ID, NAME,SALARY, CITY

     FROM EMPLOYEE1

     WHERE SALARY IS NOT NULL;

This would produce the following result:

ID	NAME	SALARY	CITY
1	A	10,000	BOMBAY
2	B	10,000	KOLKATA
3	C	10,000	CHENNAI
4	D	10,000	DELHI
5	E	12,000	NOIDA
6	F	15,000	BHUBANESWAR
7	G	11,000	PUNE
8	A	10,000	GOA
8	B	10,000	DELHI

2)Now, following is the usage of IS NULL operator:

SELECT  ID, NAME, SALARY, CITY

     FROM EMPLOYEE1    

  WHERE SALARY IS NULL;

This would produce the following result:

ID	NAME	SALARY	CITY
9	H		HYDERABAD
10	I		KILKATA