SQL查询比较(ON子句或WHERE子句中的联接条件) [英] SQL query comparison (join condition in ON clause or in WHERE clause)

问题描述

示例数据的SQL提琴链接

基本上我的疑问是，是否在 LEFT OUTER JOIN 的 ON 子句中指定条件，还是在 WHERE 中指定条件，是否相同?空检查子句?

Basically my doubt is , is it same if we specify a condition in ON clause of LEFT OUTER JOIN or we specify condition in WHERE clause with null check ?

表架构:

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子句中写入条件时，将过滤结果中的行:

• When you write condition in on clause you include all rows from App.
• When you write condition in where clause you filter rows from result:

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

In your case an App with row related with a App_Child with b.STATUS='disabled' will be filtered

这里有一个样本:

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 --

这篇关于SQL查询比较(ON子句或WHERE子句中的联接条件)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！