SQL Server IS NULL

In SQL Server 'NULL' means no value.'IS NULL' tells the SQL server to check particular column data is null.


Syntax

       SELECT  column 1,column2 ,column3  FROM TABLE WHERE column IS NULL

We are going to test some example with the following data
 

CREATE TABLE student

(

          name VARCHAR(50) NULL,

          roll INT NULL,

          adress VARCHAR(50),

          age INT NULL,

);

INSERT INTO student(name,roll,adress,age)

SELECT 'John1',123,NULL,12

UNION

SELECT 'John2',124,'35,school street',13

UNION

SELECT 'John3',125,'36,school street',14

UNION

SELECT 'John4',126,NULL,15

UNION

SELECT 'John5',127,'39,school street',16

UNION

SELECT 'John6',128,NULL,17

Here is an example of 'IS NULL'.
 

   SELECT name,roll,adress, age

              FROM student WHERE adress IS NULL

Output

name     roll   adress  age 

John1    123    NULL    12
John4    126    NULL    15
John6    128    NULL    17

Is Null checking can be applied to any query of SQL server.You can apply 'IS NULL' with insert statement.

      INSERT INTO student(name,roll,adress,age)

              SELECT name,roll,adress, age

                                FROM student WHERE adress IS NULL

       Output

     (3 row(s) affected)

Here is the data of student table. 

name     roll   adress              age  John1    123    NULL                12
John2    124    35,school street    13
John3    125    36,school street    14
John4    126    NULL                15
John5    127    39,school street    16
John6    128    NULL                17
John1    123    NULL                12
John4    126    NULL                15
John6    128    NULL                17







You can apply 'IS NULL' with update statement.
 

UPDATE student

SET age=18

WHERE adress IS NULL

Output

(4 row(s) affected)

Here is the data of student table.

name     roll   adress              age
John2    124    35,school street    13
John3    125    36,school street    14
John4    126    NULL    18
John5    127    39,school street    16
John6    128    NULL    18
John4    126    NULL    18
John6    128    NULL    18







You can apply 'IS NULL' with delete statement

            DELETE FROM student WHERE adress IS NULL

           AND age=12

     
     Output

     (2 row(s) affected)

Here is the data of student table.

name     roll   adress              age

John2    124    35,school street    13
John3    125    36,school street    14
John4    126    NULL                15
John5    127    39,school street    16
John6    128    NULL                17
John4    126    NULL                15
John6    128    NULL                17