How to show unused foreign key value when joining tables

I have 2 tables, emp, and dept(employee and department). Say I want to show the sum of all salaries per department, I could use something like:

select sum(sal), deptno
from emp
group by deptno

Now that works, but say there is a department in the list that has no employees, and I want it to show up also, how would I do that? I've tried joins, the nvl function, but no luck this far. For example, this works, but it won't show the empty departments:

select s开发者_如何学JAVAum(emp.sal), dept.deptno
from emp, dept
where emp.deptno=dept.deptno
group by dept.deptno

Thanks in advance for any help!

LEFT JOIN will do the trick:

select coalesce(sum(emp.sal),0), dept.deptno
from dept 
left join emp on emp.deptno=dept.deptno
group by dept.deptno

You should always explicitly declare your joins, so that you can change them when you need to. Implicit joins are always INNER JOIN.

Additionally, I change the query to show 0 instead of NULL when department has no employees, by using COALESCE.

You need to use an outer join

SELECT 
   dept.deptno,
   SUM(emp.sal)
FROM
   dept 
   LEFT OUTER JOIN emp ON dept.deptno = emp.deptno
GROUP BY
   dept.deptno

You want to use a LEFT JOIN so that you return all departments, regardless of whether or not they have employees.

SELECT dept.deptno, SUM(emp.sal)
    FROM dept
        LEFT JOIN emp
            ON dept.deptno = emp.deptno
    GROUP BY dept.deptno

Try this:

 SELECT ISNULL(SUM(e.sal),0) AS SalSum,
        d.DeptNo
 FROM Dept AS d
 LEFT JOIN emp AS e ON e.DeptNo = d.DeptNo
 GROUP BY d.DeptNo

Use an outer join:

select 
  sum(emp.sal), dept.deptno
from dept LEFT OUTER JOIN emp
  ON dept.deptno=emp.deptno
group by dept.deptno

Another alternative approach, justification being that it uses relational operators (union and difference) making it simple to read and understand, IMO:

select sum(sal), deptno
from emp
group by deptno

union

select 0, d.deptno
from dept d
minus
select 0, e.deptno
from emp e;

select sum(sal), deptno
from emp
group by deptno

union all

select 0, deptno
from dept
where deptno not in (select deptno from emp)

or

select sum(sal), deptno
from emp
group by deptno

union all

select 0, d.deptno
from dept d
where not exists
(
select * 
from emp e
where e.deptno = d.deptno
)