Oracle Selective Join

Question

My Table structure & data-

CREATE TABLE EMPLOYEE
   (    EMP_ID NUMBER, 
    NAME VARCHAR2(100 BYTE), 
    SALARY NUMBER, 
    DEP_ID NUMBER, 
    ORG_ID NUMBER
   );

insert into employee values (1, 'Mark', 6000, 44, 5);
insert into employee values (2, 'John', 4000, 46, 5);
insert into employee values (2, 'John', 4500, 2, 5);
insert into employee values (3, 'Terry', 5000, 44, 5);
insert into employee values (4, 'Carol', 15000, 48, 5);
insert into employee values (4, 'Carol', 15500, 4, 5);
insert into employee values (5, 'Lewis', 18000, 46, 5);

CREATE TABLE DEPARTMENT
   (    DEP_ID NUMBER, 
    DEP_NAME VARCHAR2(100 BYTE), 
    ORG_ID NUMBER
   );

insert into DEPARTMENT values (null, 'NYD', 5);
insert into DEPARTMENT values (2, 'HR', 5);
insert into DEPARTMENT values (4, 'Analyst', 5); 

In EMPLOYEE table, same employees can have 2 rows, one with a valid & other with an invalid DepartmentID. Other employees simply have one entry with an invalid DepartmentID.

I want to join EMPLOYEE & DEPARTMENT table in such a way that, if an EMPLOYEE has 2 rows, then only the row with a valid DepartmentID should be selected. If the EMPLOYEE has only 1 row with invalid DepartmentID, it should pick the default DEP_NAME (DEP_ID=null). Below is how the Query Output should look like-

| EMP_ID  |   NAME    | SALARY | DEP_NAME |
|---------|-----------|--------|----------|
|    1    |   Mark    |  6000  |   NYD    |
|    2    |   John    |  4500  |    HR    |
|    3    |   Terry   |  5000  |   NYD    |
|    4    |   Carol   |  15500 | Analyst  |
|    5    |   Lewis   |  18000 |   NYD    |

I tried different ways, but still unable to figure out. Please help

select 
    emp.EMP_ID, emp.NAME, emp.SALARY, dep.DEP_NAME
from
    employee emp
join department dep on ....
where ....

Answer 1

A bit more compactly, you can use two outer joins, one to get valid departments, one to get the default, then use NVL or COALESCE to choose between them. Lastly collapse row count down to only most desired result by using an analytical function like ROW_NUMBER:

SELECT emp_id,name,salary, dep_name
  FROM (SELECT emp_id,name,salary,NVL(d.dep_name,def.dep_name) dep_name,
               ROW_NUMBER() OVER (PARTITION BY e.emp_id ORDER BY d.dep_id) seq
          FROM employee e
               LEFT OUTER JOIN department d ON d.dep_id = e.dep_id
               LEFT OUTER JOIN department def ON def.dep_id IS NULL)
 WHERE seq = 1    

Because you asked, however, for another version that doesn't use nested query blocks, you could also get the same result with GROUP BY and some creative aggregation:

 SELECT emp_id,
        MAX(name) name,
        MAX(salary) salary,
        SUBSTR(MAX(DECODE(d.dep_name,NULL,1,2)||NVL(d.dep_name,def.dep_name)),2) dep_name
   FROM employee e
        LEFT OUTER JOIN department d ON d.dep_id = e.dep_id
        LEFT OUTER JOIN department def ON def.dep_id IS NULL
 GROUP BY emp_id               
 ORDER BY 1

This will work, but, all those semi-arbitrary aggregations and the decode/substr trick make this much less desirable than the nested query block and analytic function method above.

Answer 2

Read comments within code:

SQL> with temp as
  2    (select e.*,
  3       count(*) over (partition by e.emp_id) cnt
  4     from employee e
  5    )
  6  -- employees with two rows (one with valid, and another invalid DEP_ID)
  7  select t.emp_id, t.name, t.salary, d.dep_name
  8  from temp t join department d on d.dep_id = t.dep_id
  9  where t.cnt = 2
 10  union all
 11  -- employees with only one - invalid - DEP_ID
 12  select t.emp_id, t.name, t.salary, d.dep_name
 13  from temp t cross join department d
 14  where d.dep_id is null
 15    and t.cnt = 1
 16  order by emp_id;

    EMP_ID NAME           SALARY DEP_NAME
---------- ---------- ---------- ----------
         1 Mark             6000 NYD
         2 John             4500 HR
         3 Terry            5000 NYD
         4 Carol           15500 Analyst
         5 Lewis           18000 NYD

SQL>

Answer 3

you can use row_number() analytical function , only thing is what if you have more than 1 row with valid departeement then which one do you want to pick up.

-- untested

select * from (
select  emp.EMP_ID, emp.NAME, emp.SALARY, dep.DEP_NAME,
    ROW_NUMBER() OVER (PARTITION BY emp.EMP_ID 
    order by dep.dep_id DESC NULLS LAST)  row_n
from
    employee emp
LEFT outer join department dep on emp.dep_id = dep.dep_id
) where row_n = 1;