sql server - Sql LEFT OUTER JOIN with WHERE clause

Question

I have two tables:

Request:

 RequestID | Msg
----------------
    5  | abc
    6  | def
    7  | ghi
    8  | jkl

RequestStatus:

RequestStatusID | RequestID |StatusID
-------------------------------------
        1             5         1
        2             8         2

• Not every request has a record in RequestStatus

I need all the records from table Request except when StatusID = 2. (requestID=8 should be filter-out)

I am using LEFT OUTER JOIN to recieve the records from table Request but when I am adding Where clause (Where StatusID = 1) of course it does not work.

Answer 1

Move the constraint to your on clause.

select *
from request r
left join requestStatus rs
on r.requestID = rs.requestID
--and status_id = 1
and status_id <> 2

What's happening to you is that the outer join is performed first. Any rows coming from the outer join that don't have matches will have nulls in all the columns. Then your where clause is applied, but since 1 <> null, it's not going to work like you want it to.

EDIT: Changed on clause based on Piyush's comment.