difference between '' and "is not null"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

difference between '' and "is not null"

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-24 : 04:20:13

Today I have come across a strange situation.

I tried a code as below


select * from table1
where field1 is not null

I want all data where field1 is not blank, but the above code didn't produce the required output. It was still displaying all field1 with blank values.

Then I changed the code as below, it worked fine.


select * from table1
where field1 <> ''

I wonder if there are any difference between them. But I usually used to write "is not null"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-09-24 : 04:48:44

''(empty string) sting is different from NULL value. NULL Can be treated as a special value, which will be occupied on all datatypes.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-24 : 04:55:10

NULL and '' (empty string) are not the same.

You can handle that using ISNULL() or NULLIF()

a) where ISNULL(field1,'') <> ''
b) where NULLIF(field1,'') IS NOT NULL

Too old to Rock'n'Roll too young to die.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-24 : 06:41:51

Thanks senthil and webfred.

is there any difference between the following?


where field1 <> ''


where isnull(field1,'') <> ''

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-24 : 06:50:03

quote:
Originally posted by learning_grsql

Thanks senthil and webfred.

is there any difference between the following?

where field1 <> ''

where isnull(field1,'') <> ''

where field1 <> '' ------> Checks for only empty strings
where isnull(field1,'') <> '' -----> Checks for both NULL and empty stings

--
Chandu

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2012-09-24 : 06:53:18

I know WHERE ISNULL(field, '') <> '' would work but this query will not be searchable.
The ISNULL function in a WHERE clause is not allowed where I work becuase this...

I'd personally use

WHERE Field <> '' AND Field IS NOT NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-24 : 07:07:28

quote:
Originally posted by DaleTurley

I know WHERE ISNULL(field, '') <> '' would work but this query will not be searchable.
The ISNULL function in a WHERE clause is not allowed where I work becuase this...

I'd personally use

WHERE Field <> '' AND Field IS NOT NULL

Can you please explain the meaning of "searchable query"?
I'm not sure what you mean...

Too old to Rock'n'Roll too young to die.

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2012-09-25 : 10:37:07

Sorry, what i mean is wherever we use ISNULL in where clauses they result in index / table scans and not seeks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-25 : 15:13:29

quote:
Originally posted by DaleTurley

Sorry, what i mean is wherever we use ISNULL in where clauses they result in index / table scans and not seeks.

That's true.
Wrapping functions around indexed columns is never a good thing..

Thx for clarification

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources