Joining when key to table is null

[ghbeers]

I have a query where I want all values from TABLE.1 to be selected when the where clause is true. However, in this table there are some null values in the key field to TABLE.2 that I am joining to get a description. I am not able to pull the record's that have the null key field to the other table, but I want them and just want the description from the other table to be null.

Basically what I have is:

select
a.x,
a.y,
a.z,
d.desc
FROM TABLE.1 a
LEFT JOIN TABLE.2 d ON a.key = d.id
WHERE a.code = 'XYZ'

But some a.key are null, and I still want to display a.x, a.y, a.z for all records even when a.key is null. But everything I try will not work. I am hoping someone can show me the correct way to build this query. Thank you.

 

[ghbeers]

I ended up doing a union when key is null and when key is not null. Worked fine, but if there is a way to do this without using union, I would appreciate hearing what that is. Thank you.

 

[Olaf Doschke]

Comparison with NULL is done with field IS NULL or NOT field IS NULL. So you may try ON (a.key IS NULL AND d.id IS NULL) or (a.key=d.id), but I'd say it's the very reason primary keys are not allowed to be NULL. If you design a table in eg SSMS and make a field a primary key, which allows NULL, that action automatically unchecks the Allows Null column in the designer. So at best you have a primary key not defined as primary key. That would be bad practice.

If a relation to another table is optional you might allow NULL in foreign key fields, but that means you simply have no record to join. So it's questionable, what you're doing.

Here's the case with NULLable foreign keys, persons referencing a mother and father, which may be unknown and therefore nullable:

declare @people Table (id int IDENTITY(1,1), name nvarchar(40), motherid int NULL, fatherid int NULL)

insert into @people (name, motherid, fatherid) Values 
   (N'Wilma Feuerstein', NULL, NULL),
   (N'Fred Feuerstein', NULL, NULL),
   (N'Betty Geröllheimer',NULL,NULL),
   (N'Barney Geröllheimer',NULL,NULL),
   (N'Pebbles Feuerstein', 1, 2),
   (N'Bamm-Bamm Geröllheimer',3,4)

-by the way those are the german translations of the Flintstones tv series main characters

Select p.Name, m.Name as Mother, f.Name as Father
From @people p
Left Join @people m ON p.motherid = m.id
Left Join @people f On p.fatherid = f.id

You see you don't have to make any special join in this case, the Feuerstein and Geröllheimer parents have no known parent and their children join them as mother and father. This is totally allowed and in case of the parents, where motherid and fatherid are NULL, no mother and father is joined and those fields therefore are NULL. There's nothing special about this.

Bye, Olaf.

 

[Olaf Doschke]

Should be
[pre]--by the way those are the german translations of the Flintstones tv series main characters[/pre]
to make it a comment, of course. Sorry.

Bye, Olaf.