DiH DiH - 3 months ago 5
SQL Question

what's the difference between is not null and <>' '

Look my example, what's the difference between two codes?

Select name from customers where name is not null

Select name from customers where name <> ''

Answer

They do completely different things.

Select name from customers where name is not null

This one selects any customer who has a value in the name field. Those values can include '' as well as things like 'Sam', 'John Jones', 'pretty blonde girl'.

Select name from customers where name <> ''

This will select all names that are not null or blank In Sql Server at least. Other databases may handle this differently. The reason why it also excludes Null is that Null cannot be part of a comparison since it by definition means we don't have a clue what the value of this field is.

If you wanted to return both real names and null values and only exclude the empty strings. In SQl Server you would do:

Select name from customers where coalesce(name, 'Unknown') <>''
Comments