How to do this SQL Query with UNION construct without it producing duplicates?

Question

Here is the query im trying to make:
Show details of departments and employees including those departments with no employees.
N.B. Try this using an alternative method involving a UNION construct rather than an ‘(+)’ outer join construct.


SELECT ENAME, JOB, DEPT.DEPTNO, DNAME, LOC FROM DEPT, EMP
WHERE EMP.DEPTNO != DEPT.DEPTNO
UNION
SELECT ENAME, JOB, DEPT.DEPTNO, DNAME, LOC FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO

Any help is appreciated!

Answer 1

SELECT DISTINCT

Answer 2

Assuming that ename, job and loc all are in the emp table:



SELECT ename, job, d.deptno, dname, loc

FROM dept d JOIN emp e ON d.deptno = e.empno

UNION

SELECT NULL, NULL, deptno, dname, NULL FROM dept

WHERE deptno NOT IN (SELECT DISTINCT deptno FROM emp)



I disagree with the prior responder that the assignment indicated the instructor doesn't know what they're doing. While it's true that typically this would be done using an outer join, it never hurts to know an alternative method to a given problem.

Answer 3

You don't use UNION for this. You use LEFT JOIN -- that is, you want to include records from the left table (dept) even if they don't have matching records in the right table (emp).



select

e.ename

,e.job

,d.deptno

,d,dname

,d.loc

from dept d

left join emp e on d.deptno=e.deptno

order by d.deptno, e.ename



If this is for a class, and your teacher is asking you specifically to use UNION for this query, they really don't know what they're doing. It makes no sense to use UNION here.



@TheMadProfessor: Look at how you have to subquery select distinct to use a UNION when you're already JOINing in your first block -- no respectable programmer would implement a solution like that. It adds unnecessary redundancy to the query. It seems like the teacher wanted to make sure students knew the syntax of UNION, but couldn't come up with a real-world use case.



I agree it's a useful skill to find multiple solutions to a given problem. But it's an equally useful skill to recognize the difference between good solutions and bad ones. :)

Answer 4

Ask the guys at www.xampletech.com. They know everything to do which that kinda stuff and they are always happy to help the general public with small questions etc..