Reza Mezerji Reza Mezerji - 1 month ago 4
SQL Question

sql server: select a NULL field that is foreign key

table "product" :

productID productNAME
21 mouse
22 keyboard


table "order" :

orderID productID
31 21
32 NULL


sql command:

SELECT dbo.[order].orderID, dbo.product.productNAME
FROM dbo.[order]
INNER JOIN dbo.product ON dbo.[order].productID = dbo.product.productID


result:

orderID productID
31 mouse


QUESTION:
how to show the second order? (while productID of that order is NULL, but that order existed)
is there any trick for that?

i tried these:

WHERE (dbo.[order].orderID = 32) OR
(dbo.product.productID IS NULL)


and

SELECT dbo.[order].orderID, ISNULL(dbo.product.productNAME, ' - ') AS Expr1
FROM dbo.[order]
INNER JOIN dbo.product ON dbo.[order].productID = dbo.product.productID


but the same result

Answer

The obvious solution is left join:

SELECT o.orderID, p.productNAME
FROM dbo.[order] o LEFT JOIN
     dbo.product p
     ON o.productID = p.productID;

You could also write:

SELECT o.orderID, p.productNAME
FROM dbo.[order] o JOIN
     dbo.product p
     ON o.productID = p.productID OR o.productID IS NULL;

The OR will probably affect the performance of this query.