Replace nested "select with count" with outer join? is this possible? SELECT c.classid , c.classname , 
      (select count(s.id) from students s where s.classid = c.classid and
      status = 'failed') as noOfFailed,
      (select count(s.id) from students s where s.classid = c.classid and
      status = 'passed') as noOfPassed 
      FROM class c where c.schedule = 'MWF'


Is there a way to replace/optimize this with JOIN?
select c.classid, 
       c.classname, 
       count(case when s.status = 'failed' then 1 end) as no_of_failed,
       count(case when s.status = 'passed' then 1 end) as no_of_passed
from class c
  left join students s on c.classid = s.classid
where c.schedule = 'MWF'
group by c.classiid, c.classname;


This exploits the fact that aggregate functions will ignore null values. 

But depending on your DBMS and the size of your tables, your original query might actually be faster. But this can only be found out if you show us the execution plan.