Access vs SQL

[Erland Sommarskog]

Re: Access vs SQL

Trevor Best (nospam@besty.o rg.uk) writes:[color=blue]
> David Portas wrote:[color=green]
>> http://www.aspfaq.com/show.asp?id=2214[/color]
>
> From that site:
><---
> SQL Server handles NULL values differently. Access assumes NULL = NULL,
> so two rows where a column is <NULL> would match a JOIN clause comparing
> the two. By default, SQL Server treats NULLs correctly as UNKNOWN, so
> that, depending on the settings within SQL Server, it cannot state that
> NULL = NULL. If you are trying to determine whether a column contains a
> NULL value, the following query change should be made:
> -- Access:
> [...] WHERE column = NULL
> [...] WHERE column <> NULL
>
> -- SQL Server:
> [...] WHERE column IS NULL
> [...] WHERE column IS NOT NULL
> --->
>
> WTF? Where on Earth did that information come from? That is totally
> incorrect. Access has never treated a null as equal to another null.[/color]

An even funnier thing, is that SQL Server can behave as described for
Access above. These days you need to apply a SET command for this, but
originally, this was default behaviour for SQL Server.

Access I don't know anything about, so I believe anything you say.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Trevor Best]

Re: Access vs SQL

Erland Sommarskog wrote:[color=blue]
> Trevor Best (nospam@besty.o rg.uk) writes:
>[color=green]
>>David Portas wrote:
>>[color=darkred]
>>>http://www.aspfaq.com/show.asp?id=2214[/color]
>>
>> From that site:
>><---
>>SQL Server handles NULL values differently. Access assumes NULL = NULL,
>>so two rows where a column is <NULL> would match a JOIN clause comparing
>>the two. By default, SQL Server treats NULLs correctly as UNKNOWN, so
>>that, depending on the settings within SQL Server, it cannot state that
>>NULL = NULL. If you are trying to determine whether a column contains a
>>NULL value, the following query change should be made:
>>-- Access:
>>[...] WHERE column = NULL
>>[...] WHERE column <> NULL
>>
>>-- SQL Server:
>>[...] WHERE column IS NULL
>>[...] WHERE column IS NOT NULL
>>--->
>>
>>WTF? Where on Earth did that information come from? That is totally
>>incorrect. Access has never treated a null as equal to another null.[/color]
>
>
> An even funnier thing, is that SQL Server can behave as described for
> Access above. These days you need to apply a SET command for this, but
> originally, this was default behaviour for SQL Server.
>
> Access I don't know anything about, so I believe anything you say.[/color]

In that case you owe me $500 <g>

AFAIK no previous version of Access has behaved like that either, I
started on version 2 so I can't speak for 1.0 or 1.1. I did skip 95 and
2000, which were truly dreadful versions but I can't imagine they would
have either, unless that was one of the many bugs in them :-)

--
This sig left intentionally blank

[david epsom dot com dot au]

Re: Access vs SQL

Note: Access/Jet SQL with "=Null" is normally converted to
"is null" when you open a query in design view.

For simple queries '[fld]=null' returns the same values
in Access as '[fld] is null', but the operator precedence
is different, so an Access query that uses "=null" may
behave differently after accidental conversion.

(david)



"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns95DA132 FE435Yazorman@1 27.0.0.1...[color=blue]
> Trevor Best (nospam@besty.o rg.uk) writes:[color=green]
> > David Portas wrote:[color=darkred]
> >> http://www.aspfaq.com/show.asp?id=2214[/color]
> >
> > From that site:
> ><---
> > SQL Server handles NULL values differently. Access assumes NULL = NULL,
> > so two rows where a column is <NULL> would match a JOIN clause comparing
> > the two. By default, SQL Server treats NULLs correctly as UNKNOWN, so
> > that, depending on the settings within SQL Server, it cannot state that
> > NULL = NULL. If you are trying to determine whether a column contains a
> > NULL value, the following query change should be made:
> > -- Access:
> > [...] WHERE column = NULL
> > [...] WHERE column <> NULL
> >
> > -- SQL Server:
> > [...] WHERE column IS NULL
> > [...] WHERE column IS NOT NULL
> > --->
> >
> > WTF? Where on Earth did that information come from? That is totally
> > incorrect. Access has never treated a null as equal to another null.[/color]
>
> An even funnier thing, is that SQL Server can behave as described for
> Access above. These days you need to apply a SET command for this, but
> originally, this was default behaviour for SQL Server.
>
> Access I don't know anything about, so I believe anything you say.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Trevor Best]

Re: Access vs SQL

david epsom dot com dot au wrote:[color=blue]
> Note: Access/Jet SQL with "=Null" is normally converted to
> "is null" when you open a query in design view.
>
> For simple queries '[fld]=null' returns the same values
> in Access as '[fld] is null', but the operator precedence
> is different, so an Access query that uses "=null" may
> behave differently after accidental conversion.[/color]

No, if you put "Null" in the criteria box, it will convert to "is null"
and "not null" is converted to "is not null" but "=null" will not be
converted for some reason.

--
This sig left intentionally blank

[david epsom dot com dot au]

Re: Access vs SQL


There may be a difference in different versions of Access.

If I put "null" in the criteria box, it is immediately corrected
to "Is Null"

If I put "=null" in the SQL of a saved querydef, it is shown as
"Is Null" if the query is opened in design view. If the query
is saved in View mode, the SQL will be converted in the saved
copy. If the query is saved in SQL mode, or not saved, the original
SQL will remain.

If I put "=null" in the criteria box, it creates the SQL phrase
"Is Null". If the querydef is saved and re-opened or if the view
is switched between SQL and View mode, the view is re-created from
the SQL, and shows "Is Null"

More significantly, I had forgotten that the behaviour I
describe for '[fld]=null' is Jet 3.51. Using Jet 4.0,
'[fld]=null' returns null, rather than True.

The only useful point to take from this is that "=Null" should
not be used in Jet SQL :)

(david)

"Trevor Best" <nospam@besty.o rg.uk> wrote in message
news:41e384ed$0 $14256$db0fefd9 @news.zen.co.uk ...[color=blue]
> david epsom dot com dot au wrote:[color=green]
> > Note: Access/Jet SQL with "=Null" is normally converted to
> > "is null" when you open a query in design view.
> >
> > For simple queries '[fld]=null' returns the same values
> > in Access as '[fld] is null', but the operator precedence
> > is different, so an Access query that uses "=null" may
> > behave differently after accidental conversion.[/color]
>
> No, if you put "Null" in the criteria box, it will convert to "is null"
> and "not null" is converted to "is not null" but "=null" will not be
> converted for some reason.
>
> --
> This sig left intentionally blank[/color]