Hello All,

I am teaching myself Access and I need some help with SQL.

I have a table, Employees;

EmpID LastName FirstName
1 Smith Mary
2 Jones
3 Douglas Jane

(note there is no FirstName for EmpID 2)

My question is about the query

SELECT * from Employees where FirstName <> "Mary";

This query returns 1 record, namely the record where EmpID is 3.

I can get the result I want with the following query

SELECT * from Employees where FirstName <> "Mary" or FirstName is null;

but why do have have to add that extra step? The first query seems quite
logical to me. The FirstName in the record where EmpID does not in fact
equal "Mary" so why is it that record not returned? I can memorize that that
is the way SQL works, but it disturbs me to have to do that. I would prefer
to think it through and understand why the SQL syntax works like that. Does
this apply to SQL in general or just the SQL that is used by Access?

Help is always appreciated.

Paul

Hi:
There is a difference between Null and Blank. Null always has to be
dealt with explicitly. Blank does not.

Go to your table and in design put "" as the default for FirstName.
Delete the Jones record (Defaults don't affect records already there)
Now add a new record with Jones as the LastName.
Run the qury and you will get both non "Mary" records.

You would get the same result in SQL Server.

Good Luck
Ira Solomon

On Wed, 16 Nov 2005 05:51:11 -0500, "Paul O'Donnell"
<odonnellp (AT) rogers (DOT) com> wrote:

Thanks Ira.

"Ira Solomon" <isolomon (AT) solomonltd (DOT) com> wrote