Lessons from Relational Databases

select distinct d.name

from Departments d

where 20 > ( select count(e.ssn)

from Employees e

where d.dno = e.dno );

select distinct d.dname

from ( Departments d left-outerjoin Employees e

where d.dno = e.dno )

group by d.dno

having 20 > count(e.ssn);

Previous slide

Next slide

Back to first slide

View graphic version

In relational databases, there already some methods that unnest some of these queries. They require outerjoins and grouping. For example, this SQL query finds all departments with less than 20 employees. Its unnested form joins departments with employees, then groups by each department, and then counts the employees in each group. If a department has no employees, this too must appear at the output. This is why an outerjoin is used instead of a join.