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条件过滤)
- 官方文档