mysql - Left Outer Join 没有给出任何结果

Question

我想连接表，这些表将包含表 Portal_Order 中的所有值以及其他表中的其他匹配值。LEft outer Join 不返回任何值

查询:

With Max_Date As (Select Max(Change_Date) Change_Date, Ocoe_Job_Id 
From Ocoe_Job_Status Where Ocoe_Job_Id In 
(Select Ocoe_Job_Id From Portal_Order Where To_Char(Created_Date,'YYYY-MM-DD') 
Between ( Select To_Char(Sysdate-1000,'YYYY-MM-DD') From Dual) 
And ( Select To_Char(Sysdate,'YYYY-MM-DD') From Dual)  ) 
Group By Ocoe_Job_Id) 

Select Order_Id, Order_Name, Order_Desc, B.Description As Order_Status, C.Ocoe_Job_Id, C.Comments 
As Communication_Id , Communication_Name, Created_By, Count(*) Over () As Total_Record_Count, Row_Number()
Over ( Order By Order_Id ) Row_Number 
From  Max_Date D,
Ocoeowner.Portal_Order A
Left Outer Join Ocoeowner.Ocoe_Job_Status C On C.Ocoe_Job_Id =A.Ocoe_Job_Id
LEFT OUTER JOIN Ocoeowner.Portal_Order_Status_Code_Lk B on A.Order_Status = B.Status_Code
 Where To_Char(Created_Date,'YYYY-MM-DD') 
Between ( Select To_Char(Sysdate-1000,'YYYY-MM-DD') From Dual) And ( Select To_Char(Sysdate,'YYYY-MM-DD') From Dual)  
And C.Ocoe_Job_Id = D.Ocoe_Job_Id And D.Change_Date = C.Change_Date AND communication_name='ptuletters';

Answer 1

谓词条件需要在连接的 On 子句中，而不是在 where 子句中。外部连接的工作方式是在分析连接条件之后，将“外侧”中与内侧不匹配的所有行添加回......但这一切都发生在处理 where 子句之前。因此，如果 where 子句谓词从外部连接的外侧过滤属性，所有这些行将再次被删除......(它们都是空的)。将谓词放在连接条件中

And C.Ocoe_Job_Id = D.Ocoe_Job_Id And D.Change_Date = C.Change_Date 

必须移动，也可能是 AND communication_name='ptuletters'，具体取决于源表