Hello team,
A question, I want to figure out the names from test_tab1 which appears nowhere in the test_tab2. In the below example name from 1st table i.e. fordfigo is present in the 2nd table 'ford', only name that is not present in 2nd table is 'honda'.
create table test_tab1 (id integer, name varchar2(20))
/
insert into test_Tab1 values (1,'4aspirin');
insert into test_Tab1 values (2,'fordfigo');
insert into test_Tab1 values (3,'honda');
create table test_Tab2 (name varchar2(20))
/
insert into test_Tab2 values ('aspirin');
insert into test_Tab2 values ('ford');
insert into test_Tab2 values ('brio');
commit;
Output:3 honda
Thanks in advance!
SQL> create table test_tab1 (id integer, name varchar2(20))
2 /
Table created.
SQL>
SQL> insert into test_Tab1 values (1,'4aspirin');
1 row created.
SQL> insert into test_Tab1 values (2,'fordfigo');
1 row created.
SQL> insert into test_Tab1 values (3,'honda');
1 row created.
SQL>
SQL> create table test_Tab2 (name varchar2(20))
2 /
Table created.
SQL>
SQL> insert into test_Tab2 values ('aspirin');
1 row created.
SQL> insert into test_Tab2 values ('ford');
1 row created.
SQL> insert into test_Tab2 values ('brio');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> select *
2 from test_Tab1
3 where not exists
4 ( select null
5 from test_Tab2
6 where instr(test_Tab1.name,test_Tab2.name) > 0
7 );
ID NAME
---------- --------------------
3 honda
1 row selected.
SQL>