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

SQL Server IN Condition

SQL Server 'IN' condition is used to avoid multiple or conditions in the query.'IN' condition accept several values.Each value supplied  in the condition are compared with the data, and if  the comparison is success then the data comes to output.

 

CREATE TABLE student

(

       name VARCHAR(50) NOT NULL,

       roll INT NOT NULL,

       adress VARCHAR(50),

       age INT NOT NULL,

);

 

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

SELECT 'John1',123,'34,school street',12

UNION

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

UNION

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

UNION

SELECT 'John4',126,'38,school street',15

UNION

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

UNION

SELECT 'John6',128,'40,school street',17

Here is example of 'IN' condition multiple with string  value. 

      SELECT name,roll,adress, age

      FROM student WHERE name IN ('John3','John5','John6')

Output

name     roll   adress              age   

John3    125    36,school street    14
John5    127    39,school street    16
John6    128    40,school street    17

Here is a 'OR' condition query gives the same output.

        SELECT name,roll,adress, age

        FROM student

        WHERE name='John3'

        OR name='John5'

        OR name='John6'

Output 

name     roll   adress              age   

John3    125    36,school street    14
John5    127    39,school street    16
John6    128    40,school street    17

It should be noted that 'IN' condition can hold a number of supplied value , but it should not be the large, if large number is value supplied , internal memory exception may occur. 








Here is example of 'IN' condition with integer value.

      SELECT name,roll,adress, age

        FROM student WHERE age IN (12,14,17)

Output 

name     roll   adress              age   

John3    125    36,school street    14
John5    127    39,school street    16
John6    128    40,school street    17

'IN' condition accept  sub query, you can write query from other table in the condition. 


Here is example of in  condition with sub query

           SELECT name,

                                    roll,

                                    adress,

                                    age

                                    FROM student

                                     WHERE age IN

                                     (

                                                   SELECT age

                                                   FROM student WHERE age=12

                                     )

Output 

name     roll   adress              age   

John1    123    34,school street    12

'Not IN' condition is just reverse of the 'IN' condition. It just result value that does not match with supplied value .

Here is a example of 'Not IN' condition.

                   SELECT name,roll,adress, age

                      FROM student WHERE age NOT IN (12,14,17)

Output  

name     roll   adress              age   

John2    124    35,school street    13
John4    126    38,school street    15
John5    127    39,school street    16

SQL Server Introduction

       SQL Server is a relational database management system ,developed by Microsoft for storing and retrieve data. SQL Server is able to handle large data with multiple database of complex relational architecture. 

                 Other than storing data, SQL Server send email, automatic job scheduling, quarrying database, reading  external file, export data to external file, data analysis , reporting can be also done. 

                 SQL Server is one of the market leading relational database software written in the language C ,C++. SQL Server works on single standby computer  and  also over the network  and  support Cloud Computing also. The latest version of SQL Server is Microsoft SQL Server 2017. SQL Server support mul tilanguage application and the software is available in various language like English , German ,French ,Japanese etc. SQL Server work on various operating system like Unix ,Linux, Windows etc.

                    SQL Server support the primitive data type like Integer,String,Boolean,Binary and  advanced data type like varbinary ,image,text also.You can store integer,decimal, character text even image in the database. 

                  SQL Server contain most of the feature of advanced database management systems.It have function ,stored procedure ,views ,Trigger,table variable,reporting service etc. SQL Server work on Simple relational database principal queries and it is easy to use and have capability to hold a large data and fast execution of queries make this software very much popular among the developer and uses. You will find SQL Server is running from very small organization to the organization ,spread over several countries.