CODEWITHSUNDEEP
sqlsql-servert-sql
DiH
DiH

Reputation: 491

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

Upvotes: 6

Views: 7530

Answers (5)

FlappyBoobs
FlappyBoobs

Reputation: 1

Is not null determines if the object/record being inspected is a true null value or not (no data).

<> '' means is not an empty string, so the record does contain data (that there is an empty string) and is not actually null.

So a query with is not null will return records with a value of string.empty, and a query with <> '' will return values that are neither null nor empty. To catch both empty strings and null values you should use <> '' in your SQL statements.

Upvotes: 0

btberry
btberry

Reputation: 375

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.

Upvotes: 1

S3S
S3S

Reputation: 25112

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

Upvotes: 1

HLGEM
HLGEM

Reputation: 96542

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

Upvotes: 4

woz
woz

Reputation: 10994

Null and empty string are two different things. A given field in a table can either have no value (null) or a value of an empty string (''). The results return by each query will be mutually exclusive.

Upvotes: 0

Related Questions