Skip to main content

Simple Trick Using ISNULL

Simple Trick Using ISNULL:

Step-1: Create Sample Student Table.

CREATE TABLE Student
(
Id INT IDENTITY(1,1) PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
LivingLocation VARCHAR(100) NULL
)
Step-2 :Insert Some Sample Data

INSERT INTO Student
SELECT 'Rakesh','Hyderabad'
UNION ALL
SELECT 'Raju','Delhi'
UNION ALL
SELECT 'Madhu','Hyderabad'
UNION ALL
SELECT 'Naresh',NULL
UNION ALL
SELECT 'Venaktesh','Chennai'

Step-3 :Select Data from Student Table

SELECT * FROM Student









If we observe above data of student table we have student id 4 record LivingLocation is NULL.

Step -4 :Select Data from Student Table With Where Clause.

SELECT * FROM Student WHERE LivingLocation='Hyderabad'






In the above query we are included the where clause with LivingLocation is 'Hyderabad'

SELECT * FROM Student WHERE LivingLocation<>'Hyderabad'






In the above query we want data from Student Table whose LivingLocation is not equal to the 'Hyderabad' , but it is not selected the data whose id is '4' ,because of the id of '4' LivingLocation is NULL , but we need that record also in this situation we can we ISNULL function.

SELECT * FROM Student WHERE ISNULL(LivingLocation,'')<>'Hyderabad'




Labels:

Comments

  1. UnknownJanuary 28, 2015 at 4:29 AM

    Good one !!

    ReplyDelete
  2. UnknownJanuary 29, 2015 at 6:15 PM

    INSERT INTO Student
    VALUES('SHABARINADH','VIJAYAWADA')
    ,VALUES('Raju','Delhi')
    ,VALUES('Madhu','Hyderabad')
    ,VALUES('Naresh',NULL)
    ,VALUES('Venaktesh','Chennai')


    What about this?

    ReplyDelete
  3. AnonymousJanuary 29, 2015 at 11:05 PM

    INSERT INTO Student (StudentName,LivingLocation)
    VALUES('SHABARINADH','VIJAYAWADA'),
    ('Raju','Delhi'),
    ('Madhu','Hyderabad'),
    ('Naresh',NULL),
    ('Venaktesh','Chennai')

    ReplyDelete

Post a Comment

Popular posts from this blog

SP_MSForeachtable

Some times we need to query on the all the tables in one data base single statement. We use SP_MSForeachtable this is known as undocumented stored procedures . These all are system stored procedures. These stored procedures is place in Master database. NOTE: Please do not run all these queries in Production environment Example: create database UnDocumentedStoredProcedure use UnDocumentedStoredProcedure create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 50 ), Salary int ) insert into Emp ( Name , Salary ) values ( 'rakesh' , 8000 ),( 'raju' , 9000 ) create table Dept ( ID int identity ( 1 , 1 ), DeptName varchar ( 100 ) ) insert into Dept ( DeptName ) values ( 'CSE' ),( 'IT' ) We are created new database and also created some table with some dummy data. Select all tables data: exec sp_MSForeachtable 'select * from ?...
1 comment
Read more

SET ROWCOUNT

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...
7 comments
Read more