带有 WHERE 子句的 Sql LEFT OUTER JOIN

本文介绍了带有 WHERE 子句的 Sql LEFT OUTER JOIN的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有两张桌子:请求: 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 rleft join requestStatus rson r.requestID = rs.requestID--and status_id = 1and 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！