带有 WHERE 子句的 Sql LEFT OUTER JOIN [英] Sql LEFT OUTER JOIN with WHERE clause

问题描述

我有两张桌子:

请求:

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

请求状态:

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

• 并非每个请求在 RequestStatus
中都有记录
• Not every request has a record in RequestStatus

我需要表 Request 中的所有记录，StatusID = 2 除外.(requestID=8 应该被过滤掉)

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

我正在使用 LEFT OUTER JOIN 从表 Request 接收记录但是当我添加 Where 子句 (Where StatusID = 1)当然不行.

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.

推荐答案

将约束移至您的 on 子句.

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

发生在您身上的是首先执行外连接.来自没有匹配项的外部联接的任何行在所有列中都将具有空值.然后应用您的 where 子句，但由于 1 <> null，它不会像您希望的那样工作.

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.

根据 Piyush 的评论更改了条款.

Changed on clause based on Piyush's comment.

这篇关于带有 WHERE 子句的 Sql LEFT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！