Question from www.forosdelweb.com

I found the question below on http://www.forosdelweb.com/:

Tengo dos tablas relacionadas por el campo departamento.

en la primera el campo es clave y en la segunda no.

en la primera, los datos de ese campo son 1,2,3,4 y en la segunda tabla el departamento 4 no existe.

La pregunta es: obtener el nombre de los empleados y el nombre de los departamentos en los que trabajan,INCLUIR LOS DEPARTAMENTOS QUE NO TIENEN EMPLEADOS

select ename, deptno from emp;

The author has an EMP table and a DEPT table. The DEPT table has departments 1,2,3 and 4. The EMP table has employees in departments 1,2 and 3 but department 4 is empty. The requirement is to provide a list of ALL departments and the employees in each. I assume that an empty department should appear on one line with the employee name blank but that is not made clear.

I started by creating an EMP table as shown below. I put one employee in department 1, two in department 2 and three in department 3 although that was not a requirement of the problem. I left department 4 empty as requested):

SQL> create table emp
  2  (deptno varchar2(2),
  3  empname varchar2(10));

Table created.

SQL> insert into emp values ('1','Andrew');

1 row created.

SQL> insert into emp values ('2','Brian');

1 row created.

SQL> insert into emp values ('2','Colin');

1 row created.

SQL> insert into emp values ('3','David');

1 row created.

SQL> insert into emp values ('3','Elvis');

1 row created.

SQL> insert into emp values ('3','Finbar');

1 row created.

SQL>

Here is my DEPT table:

SQL> create table dept
  2  (deptno varchar2(2),
  3   deptname varchar2(10));

Table created.

SQL> insert into dept values ('1','Sales');

1 row created.

SQL> insert into dept values ('2','IT');

1 row created.

SQL> insert into dept values ('3','Finance');

1 row created.

SQL> insert into dept values ('4','Personnel');

1 row created.

SQL>

I can think of two ways to do this. The first one joins the two tables then uses a UNION to pick up the empty department:

SQL> col deptno format a6
SQL> select d.deptno, deptname, empname
  2  from dept d, emp e
  3  where d.deptno = e.deptno
  4  union
  5  select deptno, deptname, null
  6  from dept
  7  where deptno not in
  8  (select distinct deptno from emp)
  9  order by 1,3
 10  /

DEPTNO DEPTNAME   EMPNAME
------ ---------- ----------
1      Sales      Andrew
2      IT         Brian
2      IT         Colin
3      Finance    David
3      Finance    Elvis
3      Finance    Finbar
4      Personnel

7 rows selected.

SQL>

The second way uses an outer join:

SQL> select d.deptno, deptname, empname
  2  from dept d, emp e
  3  where d.deptno = e.deptno (+)
  4  order by 1,3
  5  /

DEPTNO DEPTNAME   EMPNAME
------ ---------- ----------
1      Sales      Andrew
2      IT         Brian
2      IT         Colin
3      Finance    David
3      Finance    Elvis
3      Finance    Finbar
4      Personnel

7 rows selected.

SQL>