IS NOT NULL vs <> ''

  • What is the difference between the below queries:

    select * from table where col IS NOT NULL and

    select * from table where col <> ''

    Thanks in advance

  • The first finds rows where the column is not NULL

    The second finds rows where the column does not equal an empty string.

    CREATE TABLE Blah (

    SomeString char(2)

    )

    Insert into Blah (null)

    Insert into Blah ('')

    Insert into blah ('abc')

    IS NOT NULL will match 2 rows, != '' will match 1.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you very much.

    How the below query works?

    Select * from table

  • Errrr, it retrieves all rows from the table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply