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

Question

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 1

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') <>''

Answer 2

There are a lot of correct answers here but I think you are missing what NULL is. It's nothing so it's not comparable to anything. Here's some test for you

DECLARE @param CHAR(1)=NULL --you can replace @param with your column name in your queries

SELECT 1 WHERE @param = NULL   --you can't compare NULL to anything using = > < <> != or any other comparision operator
SELECT 1 WHERE @param = ''     --an empty value isn't the same as a NULL value so if a NULL is present it won't be returned
SELECT 1 where @param IS NULL  --this is how you have to check for null values

--If you want to check for both empty and nulls, you can force the empty string with COALESCE or ISNULL

SELECT 1 WHERE COALESCE(@param,'') = ''
SELECT 1 WHERE ISNULL(@param,'') = ''

Answer 3

The key concept you are missing is that in SQL Server, NULL does not mean no value, it means that the value is unknown. So consider your query with some silly sample data:

DECLARE @t TABLE
    ( id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , name VARCHAR(10) NULL );
INSERT INTO @t 
VALUES
    ('dog'),
    ('cat'),
    (''),
    (NULL);

SELECT *
FROM @t 
WHERE name <> '';

What you are asking the engine is to return the records where name is not an empty string. On the evaluation of the fourth record, it is determining if NULL equals empty string or not. NULL could be empty string, we don't know ... given that the value is unknown, the engine can't include that record because you are asking for only records where we know the name is definitely not empty string.

Consider another query against the same data:

WITH cteTemp AS
    (
    SELECT *
        ,   isEqualToEmptyString = CASE WHEN name = '' THEN 'true' ELSE 'false' END 
    FROM @t 
    )
SELECT *
FROM cteTemp
WHERE isEqualToEmptyString = 'false';

Now this is written to demonstrate a point and there are cleaner ways to do the same thing (such as the COALESCE in HLGEM's answer.) But understand what is happening here: the query is first determining for sure what names are empty string (which excludes the NULL since its value is unknown) and then excluding those that are. Thus, the NULL is returned.