zno2

LEFT JOIN , RIGHT JOIN ,INNER JOIN

笛卡尔积

CREATE TABLE a(ID1 VARCHAR2(10));
CREATE TABLE b(ID2 VARCHAR2(10));
--DROP TABLE a;
--DROP TABLE b;

INSERT INTO a VALUES('a1');
INSERT INTO a VALUES('a2');

INSERT INTO b VALUES('b1');
INSERT INTO b VALUES('b2');

case1: on 条件

SELECT a.*,b.* FROM a LEFT JOIN b ON 1>0 
SELECT a.*,b.* FROM a LEFT JOIN b ON 1>0 AND b.id2<>'b1'
SELECT a.*,b.* FROM a LEFT JOIN b ON 1>0 AND b.id2<>'b1' AND b.id2<>'b2'

 case2: on 条件和where 条件

SELECT a.*,b.* FROM a LEFT JOIN b ON 1>0 AND a.id1 <>'a1'
SELECT a.*,b.* FROM a LEFT JOIN b ON 1>0 WHERE a.id1 <>'a1'

小结:

  • inner join 不保留未匹配的行
  • left join 保留左表未匹配的行(在on条件剔除后,追加左表未匹配行,然后where条件过滤)
  • right join 保留右表未匹配的行(在on 条件剔除后,追加右表未匹配行,然后where条件过滤)
  • 官方文档

 

posted on 2016-08-05 18:03  zno2  阅读(147)  评论(0编辑  收藏  举报

导航