oracle left join 没法查出左表的全部数据，很疑惑

oracle left join 无法查出左表的全部数据，很疑惑
有如下sql语句

SQL code

    select st.name,count(si.id)      from  t_school_type st   left join  t_school_info si on st.id = si.t_s_id     where si.record_status = 1  group by st.name;

t_school_type 有七条记录，
  id name
4c58f3f5-e38d-4954-9d38-c97d9e0b3331普通初中
4c58f3f6-e37d-4984-9d38-c27d3e0b3332职业初中
4c58f3f5-e38d-4954-9458-c27d360b3333九年一贯制学校
4c58f3f5-e38d-4954-9458-c27d360b3334普通小学
4c58f3f5-e38d-4954-9458-c27d360b3335特殊教育学校
4c58f3f5-e38d-4954-9458-c27d360b3336完全中学
4c58f3f5-e38d-4954-9458-c27d360b3337普通高中

t_school_info 中t_s_id 是外键

为什么查询以后的结果只有六条，左表中的数据不能去不查询出来呢，疑惑

[解决办法]
select st.name,count(si.id)
from t_school_type st,t_school_info si 
where st.id = si.t_s_id(+)
and si.record_status(+) = 1
group by st.name;
[解决办法]
select st.name,count(si.id)
from t_school_type st
 left join t_school_info si on st.id = si.t_s_id
and si.record_status = 1
group by st.name;

把where换成and试试
[解决办法]

SQL code

select st.name,count(si.id)from  t_school_type st left join  (select * from t_school_info where record_status = 1)si on st.id = si.t_s_idgroup by st.name;
[解决办法]
    select st.name,count(si.id)
     from  t_school_type st 
  left join  t_school_info si on st.id = si.t_s_id
    where si.record_status = 1
 group by st.name;
------------------------------
the result will be
STEP 1:
select * from t_school_type st left join t_school_info si on st.id = si.t_s_id;
this sql will find out every entry in table t_school_type;
we can name the result as "resulttable"

but after you add where condition
STEP 2:
select name,count(id) from resulttable where record_status=1 group by name;
the result will be filted.

So that's why you got the result like that, it's true. 

 

查看更多 下一篇