oracle左连接没用_Oracle左外连接、右外连接、完全外连接以及(+)号用法

一、准备工作

oracle连接分为：

左外连接：左表不加限制，保留左表的数据，匹配右表，右表没有匹配到的行中的列显示为null。

右外连接：右表不加限制，保留右表的数据。匹配左表，左表没有匹配到的行中列显示为null。

完全外连接：左右表都不加限制。即右外连接的结果为：左右表匹配的数据+左表没有匹配到的数据+右表没有匹配到的数据。

二、连接的语法

left/right/full outer join ...on

left/right/full join ...on

(+)号的作用：+号可以理解为补充的意思，加在那个表的列上就代表这个表的列为补充。加在右表的列上代表右表为补充，为左连接。加在左表的列上代表左表为补充，为右连接。注意：完全外连接中不能使用+号。

三、测试

准备测试数据

创建两种表，插入测试数据：

CREATE TABLE t_A (

id number,

code number,

name VARCHAR2(10)

);

CREATE TABLE t_B (

id number,

code number,

name VARCHAR2(10)

);

INSERT INTO t_A(id,code,name) VALUES(1,2,'A');

INSERT INTO t_A(id,code,name) VALUES(2,1,'B');

INSERT INTO t_A(id,code,name) VALUES(3,5,'C');

INSERT INTO t_A(id,code,name) VALUES(4,6,'D');

INSERT INTO t_A(id,code,name) VALUES(5,7,'E');

INSERT INTO t_B(id,code,name) VALUES(1,3,'AA');

INSERT INTO t_B(id,code,name) VALUES(1,4,'BB');

INSERT INTO t_B(id,code,name) VALUES(2,1,'CC');

INSERT INTO t_B(id,code,name) VALUES(1,2,'DD');

INSERT INTO t_B(id,code,name) VALUES(7,5,'GG');

查看插入的数据：

select * from t_a;

select * from t_b;

表t_a的数据

表t_b的数据

左外连接

select * from t_a a left join t_b b on a.id=b.id;

select * from t_a a,t_b b where a.id=b.id(+);

执行结果

左外连接

右外连接

select * from t_a a right join t_b b on a.id=b.id;

select * from t_a a,t_b b where a.id(+)=b.id;

执行结果

右外连接

完全外连接

select * from t_a a full join t_b b on a.id=b.id;

select * from t_a a,t_b b where a.id(+)=b.id(+); --错误语法，不支持两边(+)

执行结果

完全外连接

等值连接

select * from t_a a,t_b b where a.id=b.id;

select * from t_a a join t_b b on a.id=b.id;--等值连接也可以这样写

执行结果

等值连接

左外连接(多键值)

select * from t_a a left join t_b b on a.id=b.id and a.code=b.code;

select * from t_a a,t_b b where a.id=b.id(+) and a.code=b.code(+);

执行结果

左外连接(多键值)

右外连接(多键值)

select * from t_a a right join t_b b on a.id=b.id and a.code=b.code;

select * from t_a a,t_b b where a.id(+)=b.id and a.code(+)=b.code;

执行结果

右外连接(多键值)

完全外连接(多键值)

select * from t_a a full join t_b b on a.id=b.id and a.code=b.code;

执行结果

完全外连接(多键值)

等值连接(多键值)

select * from t_a a,t_b b where a.id=b.id and a.code=b.code;

select * from t_a a join t_b b on a.id=b.id and a.code=b.code;--等值连接也可以这样写

执行结果

等值连接(多键值)

左外连接(多键值or)

select * from t_a a left join t_b b on a.id=b.id or a.code=b.code;

select * from t_a a,t_b b where a.id=b.id(+) or a.code=b.code(+); --错误：ORA-01719: OR 或 IN 操作数中不允许外部联接运算符 (+)

执行结果

左外连接(多键值or)

右外连接(多键值or)

select * from t_a a right join t_b b on a.id=b.id or a.code=b.code;

select * from t_a a,t_b b where a.id(+)=b.id or a.code(+)=b.code; --错误：ORA-01719: OR 或 IN 操作数中不允许外部联接运算符 (+)

执行结果

右外连接(多键值or)

完全外连接(多键值or)

select * from t_a a full join t_b b on a.id=b.id or a.code=b.code;

执行结果

完全外连接(多键值or)

等值连接(多键值or)

select * from t_a a,t_b b where a.id=b.id or a.code=b.code;

select * from t_a a join t_b b on a.id=b.id or a.code=b.code;--等值连接也可以这样写

执行结果

等值连接(多键值or)

试试

select * from t_a a,t_b b where a.id=b.id(+) and a.code(+)=b.code;

// 错误：ORA-01416: 两表无法彼此外部连接

select * from t_a a left join t_b b on a.id=b.id where a.code(+)=b.code;

select * from t_a a, t_b c left join t_b b on a.id=b.id where a.code(+)=c.code;

// ORA-25156: 旧样式的外部联接 (+) 不能与 ANSI 联接一起使用

select * from t_a a

left join t_b b on a.id=b.id

left join t_b c on a.id=c.id;

select * from t_a a,t_b b,t_b c

where a.id=b.id(+) and a.id=c.id(+);

select * from t_a a

left join t_b b on a.id=b.id

left join t_b c on a.id=c.id and b.id=c.id;

select * from t_a a,t_b b,t_b c

where a.id=b.id(+) and a.id=c.id(+) and b.id=c.id(+);

select * from t_a a,t_b b,t_b c

where a.id=b.id(+) and a.id=c.id(+) and b.id=c.id;

select * from t_a a

left join t_b b on a.id=b.id

left join t_b c on a.id=c.id and b.code=c.code;

select * from t_a a,t_b b,t_b c

where a.id=b.id(+) and a.id=c.id(+) and b.code=c.code(+);

select * from t_a a,t_b b,t_b c

where b.code=c.code(+) and a.id=b.id(+) and a.id=c.id(+) ;

select * from t_a a,t_b b,t_b c

where a.id=b.id(+) and a.id=c.id(+) or a.code=c.code ;

select * from t_a a,t_b b,t_b c

where (a.id=b.id(+) and a.id=c.id(+)) or a.code=c.code ;

select * from t_a a,t_b b,t_b c

where a.id=b.id(+) and (a.id=c.id(+) or a.code=c.code) ;

多个连接条件的注意事项

Oracle中，两个表通过多个关连条件外连接的时候，如果多个条件中有没有写(+)的条件，则连接会自动变成内连接，而不再是外连接。这种情况应该是属于写SQL的失误。遇到这种情况的时候一定要注意。