Oracle Left Join 理解

Oracle Left Join 理解

表A

ID	CODE	STATUS	OUT_CODE
1	A1	0	B1

表B

ID	CODE	STATUS
1	B1	0
2	B1	1

场景一

-- 执行SQL1
SELECT * FROM A a LEFT JOIN B b ON b.CODE = a.OUT_CODE WHERE a.CODE = 'A1' AND a.STATUS = 0 AND b.STATUS = 0;

-- 执行SQL2
SELECT * FROM A a LEFT JOIN B b ON b.CODE = a.OUT_CODE AND b.STATUS = 0 WHERE a.CODE = 'A1' AND a.STATUS = 0;

SQL1和SQL2结果都为：

ID	CODE	STATUS	OUT_CODE	ID(1)	CODE(1)	STATUS(1)
1	A1	0	B1	1	B1	0

但是SQL顺序不同

• SQL1执行顺序

  先根据b.CODE = a.OUT_CODE得到数据(两张表数据根据ON条件笛卡尔积)

  ID	CODE	STATUS	OUT_CODE	ID(1)	CODE(1)	STATUS(1)
  1	A1	0	B1	1	B1	0
  1	A1	0	B1	2	B1	1

  再根据a.CODE = 'A1' AND a.STATUS = 0 AND b.STATUS = 0得到数据

  ID	CODE	STATUS	OUT_CODE	ID(1)	CODE(1)	STATUS(1)
  1	A1	0	B1	1	B1	0

• SQL2执行顺序

  先根据b.CODE = a.OUT_CODE AND b.STATUS = 0得到数据(两张表数据根据ON条件笛卡尔积)

  ID	CODE	STATUS	OUT_CODE	ID(1)	CODE(1)	STATUS(1)
  1	A1	0	B1	1	B1	0

  再根据a.CODE = 'A1' AND a.STATUS = 0 得到数据

  ID	CODE	STATUS	OUT_CODE	ID(1)	CODE(1)	STATUS(1)
  1	A1	0	B1	1	B1	0

场景二

-- 修改B表ID=1的STATUS为1
UPDATE B SET STATUS = 1 WHERE ID = '1';

-- 执行SQL1
SELECT * FROM A a LEFT JOIN B b ON b.CODE = a.OUT_CODE WHERE a.CODE = 'A1' AND a.STATUS = 0 AND b.STATUS = 0

-- 执行SQL2
SELECT * FROM A a LEFT JOIN B b ON b.CODE = a.OUT_CODE AND b.STATUS = 0 WHERE a.CODE = 'A1' AND a.STATUS = 0 

SQL1得到结果：

ID	CODE	STATUS	OUT_CODE	ID(1)	CODE(1)	STATUS(1)
NULL	NULL	NULL	NULL	NULL	NULL	NULL

SQL12得到结果：

ID	CODE	STATUS	OUT_CODE	ID(1)	CODE(1)	STATUS(1)
1	A1	0	B1	NULL	NULL	NULL

结论

LEFT JOIN先查询出左表数据，再根据ON条件得到右表数据并和左表数据做笛卡尔积，最后根据WHERE条件得到最后数据