joining two tables with nullable foreign key?
11,364
Solution 1
You just need to use a LEFT JOIN
SELECT Actions.Date, Actions.Message, Type.TypeName
FROM Actions
LEFT JOIN Type
ON Type.TypeID = Actions.FK_TypeID
If a match to Actions
is not found to tie it with Type
, then all columns in Type
will be NULL
.
Here is a good graphical visualization of the different types of joins in SQL
Solution 2
Use an OUTER JOIN
SELECT Actions.Date, Actions.Message, Type.TypeName
FROM Actions LEFT OUTER JOIN
Type ON Type.TypeID = Actions.FK_TypeID
Author by
Bonk
Updated on June 13, 2022Comments
-
Bonk about 2 years
I created two tables below, and
FK_TypeID
is nullable in this case. I want to write a query returning me the join of two tables.if
FK_TypeID
is NULL for a certain row, theTypeName
is also NULL. I am not sure how to go about creating such join statement?[Actions] ActionsID Date Message FK_TypeID //links to the table below [Type] TypeID TypeName
My current statment looks like this and it simply skips NULL
FK_TypeID
rowsSELECT * FROM Actions INNER JOIN TypeName ON Actions.FK_TypeID = [Type].TypeID
Help would be greatly appreciated!