Oracle 中On与Where选择 以及“(+)”位置不同的区别
select emp_id, emp_addr from emp_addr ;
?
?
???EMP_IDEMP_ADDR1100001上海2100002北京3100003深圳4 常德?
?select emp_id, emp_tel from emp_tel ;?
?
???EMP_IDEMP_TEL1100001139087629482100002150444427833100001134643967064 15890438076511111113456782345?
?
?--1
select a.emp_id,b.emp_tel,a.emp_addr from emp_addr a, emp_tel b
where a.emp_id=b.emp_id(+);
?
?
???EMP_IDEMP_TELEMP_ADDR110000113908762948上海210000215044442783北京310000113464396706上海4 常德5100003 深圳?
?
--2
select a.emp_id,b.emp_tel,a.emp_addr from emp_addr a, emp_tel b
where a.emp_id=b.emp_id(+)
and substr(emp_tel,1,3)='150';
?
?
???EMP_IDEMP_TELEMP_ADDR110000215044442783北京?
?
--3
select a.emp_id,b.emp_tel,a.emp_addr from emp_addr a, emp_tel b
where a.emp_id=b.emp_id(+)
and substr(b.emp_tel(+),1,3)='150';
?
?
?
???EMP_IDEMP_TELEMP_ADDR110000215044442783北京2 常德3100003 深圳4100001 上海?
?
--a? equal to 1
select a.emp_id,b.emp_tel,a.emp_addr from emp_addr a? left join emp_tel b
on a.emp_id=b.emp_id;
?
?
???EMP_IDEMP_TELEMP_ADDR110000113908762948上海210000215044442783北京310000113464396706上海4 常德5100003 深圳?
?
--b? equal to 2
select a.emp_id,b.emp_tel,a.emp_addr from emp_addr a? left join emp_tel b
on a.emp_id=b.emp_id
where? substr(emp_tel,1,3)='150';
?
?
???EMP_IDEMP_TELEMP_ADDR110000215044442783北京?
?
?
--c? equal to 3
select a.emp_id,b.emp_tel,a.emp_addr from emp_addr a? left join emp_tel b
on a.emp_id=b.emp_id
and? substr(emp_tel,1,3)='150';
?
?
?
???EMP_IDEMP_TELEMP_ADDR110000215044442783北京2 常德3100003 深圳4100001 上海?
?
?
-- not in (if chiled select exists null value , will not return )
select emp_id, emp_addr from emp_addr a where a.emp_id? not in (select emp_id from emp_addr b );
?
???EMP_IDEMP_ADDR?
-- not exists
select emp_id, emp_addr from emp_addr a where not exists (select 'a' from emp_tel b? where a.emp_id=b.emp_id);
?
?
???EMP_IDEMP_ADDR1 常德2100003深圳?
?
?
?
--? if value is not null ,count will be include
select emp_id,
???????? sum(case when substr(emp_tel,1,3) <> '150' then 1 else 0 end) as sum
??????? ,count(case when substr(emp_tel,1,3) <> '150' then 1 else 0 end) as cnt
? from emp_tel
? group by emp_id
?
?
???EMP_IDSUMCNT1100001222100002013111111114 11?
?
