SQL & PL/SQL

Hi all,

From the below two table, I want to find record from emp table where data in ename column is not matching with the f_name column of table List_name

if the text value from f_name column is matching with any of the deptno data, I want to exclude those dept.

e.g. Text 'UK02' from table List_name is available in deptno 101 from emp table. I don't want this type of record.

with emp as

(

select 101 as deptno, 'UK01' as ename, 900 as seq_num, 1000 as sal from dual union

select 101, 'UK02', 901, 2000 from dual union

select 102, 'UK001', 811, 2000 from dual union

select 102, 'UK0012', 812, 4000 from dual union

select 103, 'US001', 761, 3000 from dual union

select 103, 'US006', 762, 1000 from dual union

select 103, 'US007', 763, 4000 from dual union

select 104, 'RUS009', 201, 8000 from dual union

select 104, 'RUS001', 202, 7000 from dual union

select 105, 'AUS001', 501, 7000 from dual union

select 106, 'IND01', 321, 6000 from dual ),

List_name as

(

select 103 as id, 'US006' as f_name from dual union

select 106, 'IND01' from dual union

select 101, 'UK02' from dual union

select 107, 'SWE02' from dual )

select * from emp

where  ename not in (select f_name from list_name)

order by 1

My Output