SQL Server - Select Left Join NULL record WHERE condition

Question

I am trying to perform a SELECT query on two tables joined with a LEFT JOIN where there may not be a record in the joined table. Something like:

--SELECT row using AreaID
SELECT *
FROM Rate
LEFT JOIN Area
ON Rate.AreaID = Area.AreaID
WHERE ProductID = @ProductID
AND Area.PostcodeOutcode = @PostcodeOutcode

This works when @PostcodeOutcode exists in the Area table, but I still need to return the record in the left table if there is not a record in the right table.

I am fudging it currently by doing this, but I know there is a better solution:

DECLARE @AreaID int
SELECT @AreaID = AreaID
FROM Area WHERE PostcodeOutcode = @PostcodeOutcode 

--SELECT row using AreaID
SELECT *
FROM Rate
WHERE ProductID = @ProductID
AND
(
    AreaID = @AreaID
    OR (@AreaID IS NULL AND AreaID IS NULL)
)

I know this is probably simple, but my SQL knowledge is limited. Please help.

Thanks

Alex

Solution 1

move the area check to the join

SELECT * FROM Rate
LEFT JOIN Area 
  ON Rate.AreaID = Area.AreaID and Area.PostcodeOutcode = @PostcodeOutcode
WHERE ProductID = @ProductID 

Update for the revised question in comments, Is this what you want?

SELECT Rate.RatePercent FROM Rate
INNER JOIN Area 
  ON Rate.AreaID = Area.AreaID and Area.PostcodeOutcode = @PostcodeOutcode
WHERE 
  ProductID = @ProductID
UNION ALL
SELECT Rate.RatePercent FROM Rate 
where
  ProductID = @ProductID 
and 
  AreaId is null 
and 
 not exists(select PostCodeOutCode From Area where PostCodeOutCode=@PostCodeOutcode)

Solution 2

There is a difference for left join between these two:

Select *
From Table1 t1
Left Outer Join Table2 t2 On t2.id = t1.id
Where t2.somevalue = @SomeParameter

And

Select *
From dbo.Table1 t1
Left Outer Join dbo.Table2 t2 On t2.id = t1.id And t2.somevalue = @SomeParameter

The latter will filter Table2, while the former will filter the join between Table1 and Table2. So, this means that the first query will join all rows in the two tables on id and then filter the ones where somevalue doesn't match the parameter, i.e. this will usually also filter out the ones where somevalue is null, because there was no row.

The second query will join table1 with table2 on id, but table2 is filtered on matching parameter first, so the non-matching rows are also returned, and are thus not filtered out.

And a side note: you should always supply the schema of your tables in your queries (for performance reasons).