SQL Server Performance Forum – Threads Archive
Which is better??
Hi, I am a new learner here. I am wondering is it <b>WHERE Field_A > ”</b> better than <b>WHERE Field_A IS NOT NULL</b>?? Can anyone help to explain why? Thanks a lot!! [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
First of all, welcome here! [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />None of your WHERE clauses is "better" than the other. Since they are in fact logically completely different, you cannot even compare them with each other. <br /><br />WHERE Field_A > ” is searching for an empty string, that is a character of length 0.<br />WHERE Field_A IS NOT NULL is searching for all rows having a value at all in that column. Including an empty string. This clause will return all the row for which the value of Field_A is known at the time of execution. <br /><br />An empty string is a known value whereas NULL should represent the UNKNOWN (or missing) of a value. There might be times where you can use a DEFAULT of ” to model a missing value and there might be times where this won’t work. This Three-Valued Logic is kind of special to the SQL language and you might want to read about it in BOL or search the web. This is explained quite well in many places. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Frank, if you allow me a small correction … "WHERE Field_A > ” is searching for an empty string, that is a character of length 0." WHERE Field_A > ” is searching for a string with a length greater than 0. This could be a single space, or a string of any number of characters.
Oops, that was what I thought. Obviously it didn’t make its way from the brain to the fingers. [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br />Thanks for catching that!<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Try this CREATE TABLE Test(i CHAR(10)) INSERT INTO TEST VALUES(”)
INSERT INTO TEST VALUES(‘ ‘)
INSERT INTO TEST VALUES(NULL)
INSERT INTO TEST VALUES(‘xyz’)
INSERT INTO TEST SELECT SPACE(10) SELECT * FROM Test WHERE i > ”
SELECT * FROM Test WHERE i > SPACE(0)
SELECT * FROM Test WHERE i IS NOT NULL DROP TABLE Test
Roji. P. Thomas
http://toponewithties.blogspot.com
Thank you guys!! Arigato!! [8D]
]]>