SQL fiddle link for sample data

基本上我的疑问是，如果我们在 LEFT OUTER JOIN 的 ON 子句中指定条件或者我们在 WHERE 中指定条件是否相同带空检查的子句？

表架构:

Create table App(ID number , STATUS varchar2(10)); 
Create table App_Child(child_id number , 
                       STATUS varchar2(10), 
                       ID number ); 

查询 1

SELECT a.ID AS appID, a.STATUS AS appSTATUS, 
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID 
 FROM App  a LEFT OUTER JOIN App_Child b ON (a.id=b.id AND b.STATUS <> 'disabled')  WHERE a.ID = ?; 

查询 2

SELECT a.ID AS appID, a.STATUS AS appSTATUS, 
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID 
 FROM App  a LEFT OUTER JOIN App_Child b ON (a.id=b.id)  WHERE a.ID = ? AND (b.STATUS IS NULL OR b.STATUS<>'disabled'); 

请您参考如下方法:

结果和可读性都不一样。

• 当您在 on 子句中编写条件时，您包括 App 中的所有行。
• 当您在 where 子句中编写条件时，您从结果中过滤行:

在您的情况下，App 的行与具有 b.STATUS='disabled' 的 App_Child 相关联将被过滤

这里是一个示例:

INSERT INTO App VALUES(1,'active'); 
 
INSERT INTO App_Child VALUES(3,'disabled',1); 
 
SELECT a.ID AS appID, a.STATUS AS appSTATUS, 
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID 
 FROM App  a LEFT OUTER JOIN App_Child b  
      ON (a.id=b.id AND b.STATUS <> 'disabled')   
WHERE a.ID = 1; 
 
--- has results --- 
 
SELECT a.ID AS appID, a.STATUS AS appSTATUS, 
b.child_id AS acOWNID,b.STATUS AS acSTATUS, b.id AS acID 
 FROM App  a LEFT OUTER JOIN App_Child b ON (a.id=b.id)   
WHERE a.ID = 1 AND (b.STATUS IS NULL OR b.STATUS<>'disabled'); 
 
-- don't has results --