CODEWITHSUNDEEP
sqloracle-database
Rene
Rene

Reputation: 145

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 sum(emp.sal), dept.deptno
from emp, dept
where emp.deptno=dept.deptno
group by dept.deptno

Thanks in advance for any help!

Upvotes: 2

Views: 354

Answers (7)

onedaywhen
onedaywhen

Reputation: 57053

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;

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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.

Upvotes: 6

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

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
)

Upvotes: -1

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

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

Upvotes: 2

Lee
Lee

Reputation: 13542

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

Upvotes: 1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196162

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

Upvotes: 2

p.campbell
p.campbell

Reputation: 100607

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

Upvotes: 1

Related Questions