Re: When is a NULL Not a NULL ?

The use of a function on the join operator will preclude any utilisation of
index seek, so it might be better to specifically test for the null
possibility:

TableA INNER JOIN TableB ON TableA.ID = TableB.ID or (TableA.Id is null and
TableB.ID is null)

However, even this form will perform a lot less then joining two tables
without consideration for any null value; so if possible, you should
redesign your schema if you don't want the performace to take a dive.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ONKbd$EsGHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
That's the behaviour of NULL, by definition. NULL is considered to be
nothing at all...it's like if someone held out their empty hands and asked
you if the objects in them were the same. Well, they're not holding
anything, so the question is invalid.

In order to have them match, probably the best bet is to COALESCE the
values of each in the join. So instead of

...
TableA INNER JOIN TableB ON TableA.ID = TableB.ID
...

it would become

...
TableA INNER JOIN TableB ON COALESCE(TableA.ID, '') = COALESCE(TableB.ID,
'')
...

Note that those are two single quotes, not a double quote. You can
conceivably replace what's in the quotes with some other string, if you
want to for some reason, but since it'll only be used to make the join,
there's little reason to do so.



Rob

"Altemir" <david.altemir@xxxxxxxxx> wrote in message
news:1153872889.729811.251460@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I have a Table that I am inner joining with a View. However, the inner
join is not returning records for which the joined columns both contain
a NULL. The Table column is typed as nvarchar(50). The column in the
View is populated with NULL as a literal (e.g., SELECT NULL As ColumnA,
ColumnB, ColumnC FROM TableB).

Anyone care to guess as to why two NULL's don't form a match within
this kind of inner join?





.

Relevant Pages

  • Re: Delete Query failure
    ... FROM TableA INNER JOIN TableB ... If you are using the query grid, UNCHECK Show for any fields that are in the ... >> from which the deletion is to take place. ...
    (microsoft.public.access.queries)
  • Re: Joining Tables on Similar, but not identical, Fields?
    ... With some limitations (such as the query not being updateable, ... FROM TableA INNER JOIN TableB ... This will find records where TableA contains "Seattle, ...
    (microsoft.public.access.queries)
  • Re: SQL Statement
    ... FROM TableA INNER JOIN TableB ON TableA.FieldMany = TableB.FieldOne; ... > I am trying to write an SQL command that will display a ...
    (microsoft.public.access.queries)
  • Re: How to ignore fields in Update Query
    ... UPDATE TableA INNER JOIN TableB ON TableA.Number = TableB.Number ... > Did I set up my query wrong? ...
    (microsoft.public.access.queries)
  • Re: When is a NULL Not a NULL ?
    ... TableA INNER JOIN TableB ON TableA.ID = TableB.ID ... TableA INNER JOIN TableB ON COALESCE= COALESCE(TableB.ID, ... Note that those are two single quotes, ... there's little reason to do so. ...
    (microsoft.public.access.adp.sqlserver)