small tricky query on self join

Question

I have a table EMP with columns as below:

create table emp(
empno    number(4,0),
ename    varchar2(10),
job      varchar2(9),
mgr_id   number(4,0),
sal      number(7,2),
deptno   number(2,0));

I want to list all employees' names along with their manager names, including those who do not have a manager. For those employees, their manager's name should be displayed as 'BOSS'.

PL/SQL is associated with Oracle. I changed the tags accordingly. There is nothing tricky about this question at all. You should learn SQL so you can answer such questions. — Gordon Linoff, Jul 02 '17 at 16:23
Possible dup of https://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it. — Bob Jarvis - Слава Україні, Jul 02 '17 at 16:31

score 2 · Accepted Answer · edited Jul 02 '17 at 17:31

2

The following query should work:

select e.ename, (case when m.ename is null then 'BOSS' else m.ename end) as mgrName 
from emp e
left join emp m on m.empno = e.mgr_id

edited Jul 02 '17 at 17:31

APC

144,005
19
170
281

answered Jul 02 '17 at 16:34

Charanjith A C

68
1
3

OlivierTerrien · Answer 2 · 2017-07-02T17:07:07.853

2

To my mind, the better solution is proposed by Charanjith.

In Oracle, we could even use NVL function instead of "case when" in order to replace null value by something. The result should be the same.

select e.ename empName, NVL(m.ename, 'BOSS') mgrName from emp e
left join emp m on m.empno = e.mgr_id

Moreover, we could see another solution : using inner join to filter on emp when a manager exists. Then union for all employees who don't have any manager.

select e.ename empName, m.ename mgrName from emp e inner join emp m on e.mgr_id = m.empno
union
select e.ename empName, 'BOSS' mgrName from emp e where not exists (select 1 from emp m where e.mgr_id = m.empno)

edited Jul 02 '17 at 17:07

answered Jul 02 '17 at 16:45

OlivierTerrien

2,451
1
19
31

we can get by using like this decode (m.ename, null, 'BOSS', m.ename); – Gani Ganesh Jul 02 '17 at 17:10
1

Best case would be using `COALESCE` instead of `CASE`/`NVL`, which is Standard SQL and a shortcut for a CASE. – dnoeth Jul 02 '17 at 17:10

score 0 · Answer 3 · answered Jul 04 '17 at 08:32

0

This work fine in oracle:

SELECT e.ename,
 nvl(m.ename, 'BOSS')mgr
   FROM emp a
LEFT JOIN emp b
ON m.empno = e.mgr_id;

answered Jul 04 '17 at 08:32

Akanksha

91
7

small tricky query on self join

3 Answers3