NULL or ''

Hello all.  I just figured out that I have a problem somewhere in my SQL code.  I have many queries that Create and Update tables in my database.  I noticed that on a page that queries based on a specific column, that I get different results depending on whether I use IS NULL or = ''.  To get around this, I am using something similar to the following:

sQry = "SELECT dbDocNo, dbReqRead, dbReadComp FROM tblCompleted WHERE dbKerberos = '" & vEmp & "' AND (dbQual IS NULL OR dbQual = '') AND dbReqRead <> '' AND dbCompleted = -1"

I am not sure what is causing this.  Is it the way I am using the INSERT or UPDATE statements?  Any help would be much appreciated.

Thanks,

Mike
tmss_it_deptAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
tmss_it_dept,

In databases, Null is the absence of a value, while a zero-length string IS a value.

So, it appears that some of your values are Null, and some are a zero-length string, and that explains why you get different results when you test for just one of those conditions.

Patrick
Ron MalmsteadInformation Services ManagerCommented:
Try this...
sQry = "SELECT dbDocNo, dbReqRead, dbReadComp FROM tblCompleted WHERE dbKerberos'' = '" & vEmp & "' AND ISNULL(dbQual,'') = '' AND ISNULL(dbReqRead,'') <> '' AND dbCompleted = -1"

....isnull(field,'')...  treats null fields as "string empty"


Ron MalmsteadInformation Services ManagerCommented:
Sorry...that's

sQry = "SELECT dbDocNo, dbReqRead, dbReadComp FROM tblCompleted WHERE dbKerberos='" & vEmp & "' AND ISNULL(dbQual,'') = '' AND ISNULL(dbReqRead,'') <> '' AND dbCompleted = -1"

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

tmss_it_deptAuthor Commented:
I actually understand that, but what can I do to ensure that I get one type or another in the database?  If I understand correctly, when I am creating my INSERT or UPDATE statements and just specify a few fields as values and don't assign a zero-length string to empty fields I am getting NULL fields???  So I need to specifically enter an empty string into each and every field that I want to be empty?

Also, how can I correct the blank fields that are already in the database?

Mike
Ron MalmsteadInformation Services ManagerCommented:
You need to tell SQL how to handle NULL versus "string empty", the two values cannot be compared in that manner unless you are telling SQL to either treat string empty as null or visa versa.

So either... ISNULL(Field,'') <> ''    or    REPLACE(field,'',NULL)  IS NOT NULL

You can do an update statement to the data that is already there, but I would certainly do a backup first in case something goes awry.
tmss_it_deptAuthor Commented:
Well, this database is still pretty fresh, so any changes won't be too harsh on me.  So, in the future I need to specify it in ALL my sql statements one way or the other like UPDATE var = NULL or UPDATE var = ''?  Is there a preference of NULL versus zero-length string?

Mike
tmss_it_deptAuthor Commented:
Last question, say I have a table with 10 columns, and I have a query that enters values specifically for 5 of them, can I expect the other 5 to be NULL or an empty string?

Example:
INSERT INTO tblCompleted (Field1, Field2, Field6, Field8, Field10) VALUES (Value1, Value2, Value6, Value8, Value10)

What will the type be for Fields 3,4,5,7,9?

Thanks,

Mike
Ron MalmsteadInformation Services ManagerCommented:
Definitely a good thing to decide what you want to do early on.
I prefer NULL...rather than string empty, however this is purely a matter of opinion on design.

It helps if you simply restrict the fields to either require data or default to some value rather than null if the field is numeric type.   So for example.. I have a price column...and I don't want to constantly type code to treat NULL as zero...so I default it to 0.

Like I said, it's more of a matter of preference in your design.  Consistency will help you though, so that you don't have one field that is string empty next to another that is often NULL....then everytime you write  a statement you have to remember which is which.  It really only matters when you are comparing it to something or doing a calculation of some sort.

You'll really start to see the annoyance if you are writing an application that is constantly updating or inserting empty strings into your database or attempting calculations on a field that is null instead of zero.
Ron MalmsteadInformation Services ManagerCommented:
Example:
INSERT INTO tblCompleted (Field1, Field2, Field6, Field8, Field10) VALUES (Value1, Value2, Value6, Value8, Value10)

What will the type be for Fields 3,4,5,7,9?  ---->>>>  NULL  unless the table was designed to default to another value on that field.


Also...VALUES (Value1, Value2, Value6, Value8, Value10)
Could be...  
VALUES (NULL, NULL, NULL, NULL, NULL)
, or
VALUES ('', '', '', '', '')

Depends on your fields data types.

Also, I don't think you can send  ''   if it's a numeric type field... '' is a string...so it would either be NULL or 0 in that case.



tmss_it_deptAuthor Commented:
I really do appreciate all your help, and I don't mean to drag this post on, but to answer your question, ALL the fields  and values mentioned in above INSERT statement would be text fields. So, if I don't send a value (as in the case of 3,4,5,7,9) and I haven't set a default value in database, then they would be NULL by default?

The data in these tables can and possibly will be heavily updated, so I just want to get things correct early on.  :)

Mike
Ron MalmsteadInformation Services ManagerCommented:
""then they would be NULL by default?""  - yes they would be null.

Just remember that  ''   is  considered a value and cannot be compared to null unless you tell sql to treat it as null...and visa versa.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmss_it_deptAuthor Commented:
Thank you for this info.  Sorry for the confusion on my part.

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.