Re: NULLs in columns referencing foreign keys

In article <slrn5m2cae.ec0.dave@fast.thomases.com>, Dave Thomas <dave@fast.thomases.com> writes > >Someone in the office has got me thinking about the use of NULLs in columns >that reference other tables. > >In the schema, we have a table containing a column which references a second >table (Say a table with Individuals, where each may have a reference into a >table of addresses). However, some individuals don't have addresses, so we >store a NULL in the referencing column. > >The debate started because the comparison between the referencing column and >the referenced table's keys will return UNKNOWN when the referencing column >is NULL. I'm being told that the effect of this depends on which version of >Online you're running - some behave like our current 7.2 and don't match, >but this guy says that others will match. > >This seems pretty fundamental to me. What's the scoop? > >Thanks > >Dave > ANY CONDITION IR EXPRESSION THAT INVOLVES NULL ALWAYS EVALUATES TO NULL WHICH IS FALSE. select * from tab1 where 1=NULL - No rows found select * from tab1 wher 1!=NULL - No rows found -- David Williams