SQL Join on Null

I used to be able to join 2 tables with the following select:

select *
from B
  inner join A
  on A.Location = B.Location

But then I decided to break up the Location field into separate NestArea, 
NestNear and NestNumber fields. Since the NestNear field is almost always 
null in both tables, the following join returned very few rows:

select *
from B
  inner join A
  on A.NestArea = B.NestArea
  and A.NestNear = B.NestNear
  and A.NestNumber = B.NestNumber

The below SQL is closer, but it took about twice as long as my original 
query and, if a NestNear field is empty instead of null, doesn't return the 
row.

select *
from B
  inner join A
  on (A.NestArea = B.NestArea
  and A.NestNear = B.NestNear
  and A.NestNumber = B.NestNumber)
  or (A.NestArea = B.NestArea
  and A.NestNear = null
  and B.NestNear = null
  and A.NestNumber = B.NestNumber)

Is there a simple way to retrieve rows for "empty(A.NestArea) = 
empty(B.NestArea)" using SQL? I'm using the latest version of dBASE with 
Level 7 DBF files.

Clay 

You might try FULL OUTER JOIN

Greg Hill

"Clay" <clayg@msn.com> wrote in message
news:IK5QjDzhIHA.2000@news-server...
>I used to be able to join 2 tables with the following select:
>
> select *
> from B
>  inner join A
>  on A.Location = B.Location
>
> But then I decided to break up the Location field into separate NestArea, 
> NestNear and NestNumber fields. Since the NestNear field is almost always 
> null in both tables, the following join returned very few rows:
>
> select *
> from B
>  inner join A
>  on A.NestArea = B.NestArea
>  and A.NestNear = B.NestNear
>  and A.NestNumber = B.NestNumber
>
> The below SQL is closer, but it took about twice as long as my original 
> query and, if a NestNear field is empty instead of null, doesn't return 
> the row.
>
> select *
> from B
>  inner join A
>  on (A.NestArea = B.NestArea
>  and A.NestNear = B.NestNear
>  and A.NestNumber = B.NestNumber)
>  or (A.NestArea = B.NestArea
>  and A.NestNear = null
>  and B.NestNear = null
>  and A.NestNumber = B.NestNumber)
>
> Is there a simple way to retrieve rows for "empty(A.NestArea) = 
> empty(B.NestArea)" using SQL? I'm using the latest version of dBASE
with 
> Level 7 DBF files.
>
> Clay 

On Sat, 15 Mar 2008 23:19:25 -0700, in dbase.getting-started, 
Subject: SQL Join on Null, 
Message-ID: <IK5QjDzhIHA.2000@news-server>,  
"Clay" <clayg@msn.com> wrote:


>select *
>from B
>  inner join A
>  on (A.NestArea = B.NestArea
>  and A.NestNear = B.NestNear
>  and A.NestNumber = B.NestNumber)
>  or (A.NestArea = B.NestArea
>  and A.NestNear = null
>  and B.NestNear = null
>  and A.NestNumber = B.NestNumber)

See localSQL.hlp on IS NULL predicate.

Indicates whether a column contains a NULL value.
column_reference IS [NOT] NULL

Description
Use the IS NULL comparison predicate to filter a table based on the specified
column containing a
NULL (empty) value.

SELECT *
FROM Customer
WHERE (InvoiceDate IS NULL)

Use NOT to return the converse of a IS NULL comparison.

Note	For a numeric column, a zero value is not the same as a NULL value.

Ivar B. Jessen Wrote:

Thanks, but IS NULL doesn't work for my join. I'm trying to join 2 NULLs or
possibly 2 empty fields or possibly 1 NULL and 1 empty field.

Clay

> 
> See localSQL.hlp on IS NULL predicate.
> 
> Indicates whether a column contains a NULL value.
> column_reference IS [NOT] NULL
> 
> Description
> Use the IS NULL comparison predicate to filter a table based on the
specified column containing a
> NULL (empty) value.
> 
> SELECT *
> FROM Customer
> WHERE (InvoiceDate IS NULL)
> 
> Use NOT to return the converse of a IS NULL comparison.
> 
> Note	For a numeric column, a zero value is not the same as a NULL value.
> 
> 
> Ivar B. Jessen

Thanks for the suggestion, but a FULL OUTER JOIN also returns rows that don't
have a match. After much soul searching, I did manage to find a solution that
works perfectly:
SELECT *
FROM B
   INNER JOIN A
    ON (A.NestArea || A.NestNumber || A.NestNear = B.NestArea || B.NestNumber ||
B.NestNear)

It seems that concatenating NULL data in SQL doesn't result in a NULL. Instead,
the NULL data is treated like empty data, exactly what I was looking for.

Clay


Greg Hill Wrote:

> You might try FULL OUTER JOIN
> 
> Greg Hill
> 
> "Clay" <clayg@msn.com> wrote in message
news:IK5QjDzhIHA.2000@news-server...
> >I used to be able to join 2 tables with the following select:
> >
> > select *
> > from B
> >  inner join A
> >  on A.Location = B.Location
> >
> > But then I decided to break up the Location field into separate
NestArea, 
> > NestNear and NestNumber fields. Since the NestNear field is almost
always 
> > null in both tables, the following join returned very few rows:
> >
> > select *
> > from B
> >  inner join A
> >  on A.NestArea = B.NestArea
> >  and A.NestNear = B.NestNear
> >  and A.NestNumber = B.NestNumber
> >
> > The below SQL is closer, but it took about twice as long as my
original 
> > query and, if a NestNear field is empty instead of null, doesn't
return 
> > the row.
> >
> > select *
> > from B
> >  inner join A
> >  on (A.NestArea = B.NestArea
> >  and A.NestNear = B.NestNear
> >  and A.NestNumber = B.NestNumber)
> >  or (A.NestArea = B.NestArea
> >  and A.NestNear = null
> >  and B.NestNear = null
> >  and A.NestNumber = B.NestNumber)
> >
> > Is there a simple way to retrieve rows for "empty(A.NestArea) = 
> > empty(B.NestArea)" using SQL? I'm using the latest version of
dBASE with 
> > Level 7 DBF files.
> >
> > Clay 
> 
>