Suppose I have table t1:

CREATE TABLE t1
(BHID BIGINT NOT NULL,
CONTROLLER INTEGER,
LOCATION INTEGER,
PREFIX CHARACTER(5),
REGNUM INTEGER,
TAG CHARACTER(5),
HERD_ID CHARACTER(15),
SEX CHARACTER(1),
SIRE_BHID BIGINT,
DAM_BHID BIGINT,
RECIP_BHID BIGINT,
COLOR CHAR(2),
-- lots left out for brevity
)

Most of the columns are codes of one form or another. Note that none of
them are "NOT NULL" except for the first, which is the key.

t2 is defined as:

CREATE TABLE t2
(ID INTEGER NOT NULL GENERATED
NAMEx VARCHAR(30) NOT NULL,
LEFTx INTEGER NOT NULL,
RIGHTx INTEGER NOT NULL,
ENTITY_ID INTEGER NOT NULL
)

t5 is defined by

CREATE TABLE IS.COLOR_DEFN
(COLOR_CODE CHARACTER(2) NOT NULL,
COLOR_DESC CHARACTER(50)
)

Right now I am creating (for our occasional users) a view like:

CREATE VIEW t1v
AS select t1.herd_id as animal_id,
t2.namex as locname,
t3.herd_id as sire_id,
t3.namex as sire_name,
t4.herd_id as dam_id,
t4.namex as dam_name,
t5.color_name
t1.BHID, t1.CONTROLLER,
t1.TATTOO,
t1.ASSOC, t1.PREFIX, t1.REGNUM,
t1.TAG,
t1.HERD_ID,
t1.SEX,
t1.BIRTH_DATE,
t1.NAMEX,
t1.ACTIVEX,
t1.ELECTRONIC_ID,
t1.PATHFINDER,
t1.NOTES,
t1.DISPOSAL_CODE, t1.DISPOSAL_DATE,
t1.LOCATION, t1.SIRE_BHID, t1.DAM_BHID
from t1,
t2,
t1 t3,
t1 t4
t5
where t1.location=t2.id
and t5.color_code=t1.color
and t1.sire_bhid=t3.bhid
and t1.dam_bhid=t4.bhid;

All of this works swimmingly as long as none of the things looked up
from t1 (sire_bhid, dam_bhid, location, color) are not null.
Unfortunately, rows with a NULL in any of these columns causes the
SELECT (with, AFAIK an implicit INNER JOIN) to drop the row. Up until
the addition of colname I "solved" the problem by defining a zero bhid
and location and forcing "NOT NULL". This not an appropriate strategy as
more lookup columns, like color, get added to the table. Is there a way
to get all of the rows? LEFT OUTER JOIN is almost right but it seems to
apply to the case of a missing row in the target table, not a NULL in
the the base table, but that may just be my inexperience with SQL speaking.

Robert Stearns <rstearns1241 (AT) charter (DOT) net> wrote

[]>
In what ways is an OUTER JOIN not right for what you want?
simplified to this:
table1 has
PK bhid, colorid and name
1 1 "the first"
2 3 "the second"
3 NULL "colorless"

table2 has
PK cid, color
1 black and white
2 black and brown
3 solid brown

then
SELECT name, colorid, cid, color
from table1, table2
where colorid = cid ;

yields

"the first" 1 1 black and white
"the second" 3 3 solid brown

then the outer join
SELECT name, colorid, cid, color
from table1, table2
where table1 left outer join table2 on colorid = cid ;

(excuse me if my syntax is a little off. I've been corrupted by ORACLE
syntax).

should give this:
"the first" 1 1 black and white
"the second" 3 3 solid brown
"the first" 1 NULL NULL
"the second" 3 NULL NULL
"colorless" NULL NULL NULL

so if we filter out the NULL's on cid, we mistakenly get rid of the
"colorless" row. We need rows where the color id's are equal or are
both null. Using the previous query as an in-line view we can write
this easily:

SELECT name, colorid, cid, color
from ( SELECT name, colorid, cid, color
from table1, table2
where table1 left outer join table2 on colorid = cid )
where colorid = cid OR ( colorid is null AND cid is null )
;

It is a little cumbersome. Depending on your database (You never
mentioned which DBMS you use), you might be able to simplify this a
bit.

HTH,
Ed

As I said, I thought LEFT OUTER JOIN only applied where the target row
was missing, not when the source key was NULL. I'll try your solution
(under DB2) and see what happens.

Ed prochak wrote: