Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

NULL vs ''

Status
Not open for further replies.

DBAWinnipeg

Programmer
Joined
Apr 14, 2004
Messages
173
Location
CA
Can someone tell me the difference between NULL and ''

When I do a select statement with the where clause of IS NULL nothing comes up

but

When I do a select statement with the where clause of ''

I return rows.

and it's open quote, close quote there is no space, no characters, nothing

Can someone please explain
 
NULL <> ''

Basic definition of a NULL value is that it is an UNKNOWN value.

NULL could be a space, it could be a 1, it could be APPLE....

When a value is NULL, replace it in your mind with "I don't know what this value is".

Then WHERE mycolumn IS NULL
becomes WHERE I don't know what the value in mycolumn is.

A '' (Blank space or nothing) is actually something. It's a blank. It can't be a 1, it can't be APPLE, it can't be anything but a blank. So it's not NULL.

Hope that helps.

-SQLBill
 
The value NULL as it is used in SQL is a place holder for an unknown value. That means that the database engine doesn't know what value might be there, it only knows that there isn't any value there now. Because of that, any comparison with NULL will fail under the SQL-92 standard... That doesn't mean that the comparison is FALSE (it is actually UNKNOWN), but that the logical execution path for FALSE will be chosen.
i.e. try this but write down what you expect to happen first
Code:
IF 1 != NULL
SELECT 'Yay'
ELSE
SELECT 'No Yay'

IF 1 = NULL
SELECT 'Yay'
ELSE
SELECT 'No Yay'

The empty string is exactly that a string of no length - which isnt the same as I dont know i.e. NULL
Hope this helps - its my understanding of it anyway


"I'm living so far beyond my income that we may almost be said to be living apart
 
NULL means no information is known. '' means an empty string. NULL never matches anything - that is why it has its own operator IS NULL. A blank string of 0 bytes is not the same as NULL. Don't ask me why - something to do with a Codd I think.
 
Each data type has it's own domain of values. Integers can go from -something to +something2, strings have ASCII bytes etc. If value belongs to domain, then it isn't "unknown" by definition.

If column has defined foreign key constraint, this further restricts domain of values. Using '' or something for a primary key value is kind of stupid, plus when PK value exists in table it is known by definition.

To put it simple: NULL describes "unknown" state in a singular, typeless, application-independent fashion.
 
In other database systems such as Oracle, '' is equivalent to NULL. This may be part of the confusion.

-------------------------------------
A sacrifice is harder when no one knows you've made it.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top