Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 20 July 2017

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                       



Share this article :

0 comments:

Post a Comment