关于左连接、右连接、外接连总结 在9i以前可以这么写: 左联: select a.id,a.name,b.address from a,b where a.id=b.id(+) 右联: select a.id,a.name,b.address from a,b where a.id(+)=b.id 外联 SELECT a.id,a.name,b.address FROM a,b WHERE a.id = b.id(+) UNION SELECT b.id,'' name,b.address FROM b WHERE NOT EXISTS ( SELECT * FROM a WHERE a.id = b.id); 在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成: 默认内部联结: select a.id,a.name,b.address,c.subject from (a inner join b on a.id=b.id) inner join c on b.name = c.name where other_clause 左联 select a.id,a.name,b.address from a left outer join b on a.id=b.id where other_clause 右联 select a.id,a.name,b.address from a right outer join b on a.id=b.id where other_clause 外联 select a.id,a.name,b.address from a full outer join b on a.id=b.id where other_clause or select a.id,a.name,b.address from a full outer join b using (id) where other_clause
你好。能解释一下:select a.id,a.name,b.address from a,b where a.id=b.id(+)这句话的意思吗?解释一下a.id=b.id(+)的意思就可以。这句话意思是不是说 a的id必须等于b的id,但是说,如果a的id字段不为null,b的为null,也是可以的?谢谢回复~
select a.id,a.name,b.address from a,b where a.id=b.id(+)--左连接下面是等同的 select a.id,a.name,b.address from a left join b on(a.id=b.id) 这里面除了匹配a.id=b.id 还有表A中有的表b中没有的
在9i以前可以这么写:
左联:
select a.id,a.name,b.address from a,b
where a.id=b.id(+)
右联:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id
外联
SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:
默认内部联结:
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause
左联
select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause
右联
select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause
外联
select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause
where a.id=b.id(+)这句话的意思吗?解释一下a.id=b.id(+)的意思就可以。这句话意思是不是说
a的id必须等于b的id,但是说,如果a的id字段不为null,b的为null,也是可以的?谢谢回复~
记住哪边的数据也许不齐全就在哪边补(+)
也就是左联接(+)在右
也就是右联接(+)在左
不过这已经是oracle旧版本的
新版本不提倡用(+)
where a.id=b.id(+)--左连接下面是等同的
select a.id,a.name,b.address from a left join b on(a.id=b.id)
这里面除了匹配a.id=b.id 还有表A中有的表b中没有的