Oracle中LEFT JOIN后AND与WHERE的异同-Toy模板网

这篇具有很好参考价值的文章主要介绍了Oracle中LEFT JOIN后AND与WHERE的异同。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1、AND 过滤之后再连接

2、WHERE 连接之后再过滤

下面以具体例子来说明：

(1)、建表及插入测试数据

--建测试表
create table FACT_TAB
( ID     INTEGER,
  STATUS VARCHAR2(8)
);
create table DIM_STATUS
( STSTUS_CLASS VARCHAR2(8),
  STATUS_CODE  VARCHAR2(8),
  STATUS_DESC  VARCHAR2(8)
);
--插入测试数据
insert into FACT_TAB (ID, STATUS)values (1, '1');
insert into FACT_TAB (ID, STATUS)values (2, '1');
insert into FACT_TAB (ID, STATUS)values (3, '2');
insert into FACT_TAB (ID, STATUS)values (4, '1');
insert into FACT_TAB (ID, STATUS)values (5, '2');
insert into FACT_TAB (ID, STATUS)values (6, '3');
insert into FACT_TAB (ID, STATUS)values (7, '1');
insert into FACT_TAB (ID, STATUS)values (8, '2');
insert into FACT_TAB (ID, STATUS)values (9, '3');
insert into FACT_TAB (ID, STATUS)values (10, '3');
insert into FACT_TAB (ID, STATUS)values (11, '2');
insert into FACT_TAB (ID, STATUS)values (12, '1');

insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('1', '1', '正常');
insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('1', '2', '注销');
insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '1', '正常');
insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '2', '注销');
insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '3', '遗失');
insert into DIM_STATUS (STSTUS_CLASS, STATUS_CODE, STATUS_DESC)values ('2', '4', '未知');

commit;

(2)、on后面and 条件表示先过滤之后，再连接

以下两种写法，所得的结果相同：

SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC
  FROM FACT_TAB A
  LEFT JOIN DIM_STATUS B
    ON A.STATUS = B.STATUS_CODE
   AND B.STSTUS_CLASS = '2'
   AND B.STATUS_CODE = '1'
 ORDER BY A.ID;

SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC
  FROM FACT_TAB A
  LEFT JOIN (SELECT * FROM DIM_STATUS WHERE STATUS_CODE = '1') B
    ON A.STATUS = B.STATUS_CODE
   AND B.STSTUS_CLASS = '2'
--AND B.STATUS_CODE = '1'
 ORDER BY A.ID;

Oracle中LEFT JOIN后AND与WHERE的异同,CSDN/nsj820迁移,Oracle,oracle,数据库,Left Join,where与AND的使用

(3)、on后面where条件表示先关联之后，再过滤

SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC
  FROM FACT_TAB A
  LEFT JOIN DIM_STATUS B
    ON A.STATUS = B.STATUS_CODE
   AND B.STSTUS_CLASS = '2'
 WHERE B.STATUS_CODE = '1'
 ORDER BY A.ID;

Oracle中LEFT JOIN后AND与WHERE的异同,CSDN/nsj820迁移,Oracle,oracle,数据库,Left Join,where与AND的使用

(4)、全值的情况

--全值的情况
SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC
  FROM FACT_TAB A
  LEFT JOIN DIM_STATUS B
    ON A.STATUS = B.STATUS_CODE
   AND B.STSTUS_CLASS = '2'
 ORDER BY A.ID;

Oracle中LEFT JOIN后AND与WHERE的异同,CSDN/nsj820迁移,Oracle,oracle,数据库,Left Join,where与AND的使用

(5)、说明：

用到此类连接的情况，多为事实表为主表，维表为次表的代码关联的连接；JOIN后AND与WHERE的区别，其实就是主次表过滤与联接的先后问题，这一点能认识到，所有得出的结果，都不难理解了；另外，如果主表或次表的联接关键字，有多个重复记录，则联接的结果会催生出多条重复记录，这就要求联接的关键字根据需要须是事实上的主键。文章来源地址https://www.toymoban.com/news/detail-690076.html

附：上面讨论的是对辅表限制的情况，下面的是对事实表限制用on和where的情况

1、对事实表(主表)用on
将在结果集中过滤不满足主表条件的辅表信息，但因为是左连接主表的所有记录都会显示出来。
SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC
  FROM FACT_TAB A
  LEFT JOIN DIM_STATUS B
    ON A.STATUS = B.STATUS_CODE
   AND B.STSTUS_CLASS = '2'
      --AND B.STATUS_CODE = '1'
   AND A.ID IN ('1', '2', '3')
 ORDER BY A.ID;
2、对事实表(主表)用where
如果对主表的限制放在where里，则不满足主表条件的所有主表辅表信息记录将都不会体现在结果集中。
SELECT A.ID, NVL(B.STATUS_CODE, '-1') STATUS_CODE, B.STATUS_DESC
  FROM FACT_TAB A
  LEFT JOIN DIM_STATUS B
    ON A.STATUS = B.STATUS_CODE
   AND B.STSTUS_CLASS = '2'
--AND B.STATUS_CODE = '1'
 WHERE A.ID IN ('1', '2', '3')
 ORDER BY A.ID;
注：因为对主表用ON限制没什么意义，所以基本上用不到。

到了这里，关于Oracle中LEFT JOIN后AND与WHERE的异同的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！