Pulling null records in a SQL

vinuseba · New User Joined: 17 Feb 2014 Posts: 19 Location: India

hi ,

The field VARA is declared as below in the DDL of TABLEA.

VARA CHARACTER(2) FOR MIXED DATA
WITH DEFAULT NULL

While using VARA in in an SQL do i need to give a condition specificaly in the where clause as "VARA is NULL" to get the null values in the output?

Because the result of below two quries is different.
QUERY-1
Select count(*) from TABLEA where VARA <> 'AB' ;

QUERY-2
Select count(*) from TABLEA where VARA <> 'AB' or VARA is null ;

Pandora-Box · Posted: Fri Apr 11, 2014 12:49 pm

Is the requirement to get "only" NULL rows?

vinuseba · New User Joined: 17 Feb 2014 Posts: 19 Location: India

no the requirement is to get all the rows(including nulls) expect VARA not equal to 'AB'

Rohit Umarjikar · Posted: Fri Apr 11, 2014 1:22 pm

What are the values are you getting?

Also can you please show us the maked output of below query ?

vinuseba · New User Joined: 17 Feb 2014 Posts: 19 Location: India

Please find the output of the query below.

Query
Select VARA from TABLEA where VARA is null
FETCH FIRST 10 ROWS ONLY ;

Result:
VARA
-----
-
-
-
-
-
-
-
-
-
-

GuyC · Posted: Fri Apr 11, 2014 1:46 pm

Select VARA from TABLEA where VARA is distinct from 'AB'

vinuseba · New User Joined: 17 Feb 2014 Posts: 19 Location: India

Select VARA from TABLEA where VARA is distinct from 'AB'
is meeting our requirement ; but my concern is if we donot give "VARA is NULL" condition in the where condition wont it pull the null records?

chandan.inst · Posted: Fri Apr 11, 2014 5:21 pm

It won't because you can not compare Null with any value.
So with

vinuseba · New User Joined: 17 Feb 2014 Posts: 19 Location: India

thank you all ...now I got it....