SQL is NULL AND is NOT NULL

What is Null values:

SQL NULL is an undefined and incomparable value.

It is not equal to zero or space.

It will not occupy any memory.

It is represented by NULL keyword and displayed as space.

It is a standard value supported by every RDBMS tool defined by EF code.

It provides unique treatment for all types of data any athematic operation with null values gives null.

Is null/is not null:

Used to compare null values

Supports with all types of data

The following statement List the employees who are not having commission.

SELECT ename, sal, comm

  FROM emp

 WHERE comm IS NULL;

The following statement List the employees who are having commission.

SELECT ename, sal, comm

  FROM emp

 WHERE comm IS NOT NULL;

The following statement Assign job as executive if job is null.

UPDATE emp

   SET job = 'EXECUTIVE'

 WHERE job IS NULL;

The following statement Assign dept as 10 if deptno is null.

UPDATE emp

   SET hiredate = '19-NOV-2011'

 WHERE hiredate IS NULL;

The following statement removing a column where comm having 5000

UPDATE emp

   SET comm = NULL

 WHERE comm = 5000; 

SQL is NULL or empty

The following statement returns values where commission having NULL or EMPTY

SELECT *

  FROM emp

 WHERE (comm IS NULL OR comm = '');

SQL is NOT NULL or empty

The following statement returns values where commission having NOT NULL or EMPTY

SELECT *

  FROM emp

 WHERE (comm IS NOT NULL OR comm = '');

SQL is NULL or 0(zero)

The following statement returns values where commission having NULL or 0(ZEO)

SELECT *

  FROM emp

 WHERE (comm IS NULL OR comm =0);

You May Like:                                              

                            SQL IN Operator

                            SQL Like Operator

                            SQL Between Operator