post-filter the results in oracle

Question

What would be the most performant oracle query (not a procedure) for searching the jobs which have only inactive employees for the below tables. *I cannot change DB structure.

1. Job table

id	name
0	j0
1	j1
2	j2
3	j3
4	j4

...

2. JoinTable table

id	job_id	employee_id
0	0	0
1	0	1
2	1	0
3	1	2
4	2	2
5	2	3
6	3	2
7	3	4
8	4	3

...

3. Employee table

id	status
0	active
1	active
2	inactive
3	inactive
4	null

...

Result should be like:

name
j2
j4

Create and inserts:

CREATE TABLE job(
    id int,
    name char(50)
);
CREATE TABLE jointable(
    id int,
    job_id int,
    employee_id int
);
CREATE TABLE employee(
    id int,
    status char(50)
);
INSERT INTO job (id, name) VALUES (0, 'j0');
INSERT INTO job (id, name) VALUES (1, 'j1');
INSERT INTO job (id, name) VALUES (2, 'j2');
INSERT INTO job (id, name) VALUES (3, 'j3');
INSERT INTO job (id, name) VALUES (4, 'j4');
INSERT INTO employee (id, status) VALUES (0, 'active');
INSERT INTO employee (id, status) VALUES (1, 'active');
INSERT INTO employee (id, status) VALUES (2, 'inactive');
INSERT INTO employee (id, status) VALUES (3, 'inactive');
INSERT INTO employee (id, status) VALUES (4, null);
INSERT INTO jointable (id, job_id, employee_id) VALUES (0, 0, 0);
INSERT INTO jointable (id, job_id, employee_id) VALUES (1, 0, 1);
INSERT INTO jointable (id, job_id, employee_id) VALUES (2, 1, 0);
INSERT INTO jointable (id, job_id, employee_id) VALUES (3, 1, 2);
INSERT INTO jointable (id, job_id, employee_id) VALUES (4, 2, 2);
INSERT INTO jointable (id, job_id, employee_id) VALUES (5, 2, 3);
INSERT INTO jointable (id, job_id, employee_id) VALUES (6, 3, 2);
INSERT INTO jointable (id, job_id, employee_id) VALUES (7, 3, 4);
INSERT INTO jointable (id, job_id, employee_id) VALUES (8, 4, 3);

I've tried something like:

select j.name from job j
left join jointable jt on j.id = jt.job_id
left join employee e on jt.employee_id = e.id
where e.status = 'inactive'
-- where not exists(select status from employee where status <> active and status <> null)
-- where exists(select status from employee where status = 'inactive')
order by j.id desc;

I'm not sure how to incroporate in, having, count or proper where clauses to achive the reqirement.. or maybe i should use something else. Thanks in advance.

Answer 1

Maybe a little more performant than Ankit's query as this one fetches data from each of your tables only once.

SQL> with temp as
  2    (select j.name,
  3            min(nvl(e.status, '-')) min_status,
  4            max(nvl(e.status, '-')) max_status
  5     from job j join jointable t on t.job_id = j.id
  6                join employee e on e.id = t.employee_id
  7     group by j.name
  8    )
  9  select x.name
 10  from temp x
 11  where x.min_status = x.max_status
 12    and x.min_status = 'inactive';

NAME
----
j2
j4

SQL>

Answer 2

I think you may try below query -

SELECT DISTINCT J.name
  FROM Job J
  JOIN JoinTable JT ON J.id = JT.job_id
  JOIN Employee E ON JT.employee_id = E.id
 WHERE E.status = 'inactive'
   AND NOT EXISTS (SELECT NULL
                     FROM Employee E2
                     JOIN JoinTable JT2 ON JT2.employee_id = E2.id
                    WHERE COALESCE(E2.status, 'I') <> 'inactive'
                      AND J.id = JT2.job_id);

Fiddle Demo.