[Q]怎么实现左联,右联与外联 [A]在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
Inner Joins An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition. 像a.id=b.id这种情况就是内连接了,对不对?
例如有A,B表,A (ID,FIELD1) 数据 1 TEST B (ID,FIELD2) 数据: 1 test1 2 test2 select b.id ,b.field2 from a,b where a.id = b.id 结果: 1 test1 select b.id,b.field2,a.field1 from a,b where a.id (+) = b.id 结果: 1 test1 test 2 test2 null
select b.id,b.field2,a.field1 from a,b where a.id (+) = b.id是否等价于 select b.id,b.field2,a.field1 from b left outer join a on a.id = b.id;这些语句都很容易试,但我还是不清楚,什么情况下要用到连接?连接是不是就是条件?
外连接是指有一方无数据但要显示全部
最后的是指2边都有可能没有的也全显示
要求查询的结果集中出现所有B中的ID,则用a.id(+)=b.id
至于a.id=b.id的情况就不用解释了吧。
[A]在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
An inner join (sometimes called a "simple join") is a join of two or more tables that
returns only those rows that satisfy the join condition.
像a.id=b.id这种情况就是内连接了,对不对?
数据 1 TEST
B (ID,FIELD2)
数据: 1 test1
2 test2
select b.id ,b.field2 from a,b where a.id = b.id
结果: 1 test1
select b.id,b.field2,a.field1 from a,b where a.id (+) = b.id
结果: 1 test1 test
2 test2 null
select b.id,b.field2,a.field1 from b left outer join a on a.id = b.id;这些语句都很容易试,但我还是不清楚,什么情况下要用到连接?连接是不是就是条件?