How to use subquery to drop rows from Tab1 which are in Tab2 in Oracle SQL?-CodePudding

I have tables in Oracle SQL like below:

Tab1

ID
-----
1
2
3

Tab2

ID
-----
3
4
5

And I need to take values from Tab1 which are not in Tab2. I made query like below:

select ID
from Tab1
where ID not in (select ID from Tab2)

Above query does not work, how can I change it to achieve result as I need:

ID
---
1
2

I can add that I prefere to use subquery in this problem, how can I do that in Oracle SQL ?

CodePudding user response：

With the MINUS set operator:

SQL> with
  2  tab1 (id) as
  3    (select 1 from dual union all
  4     select 2 from dual union all
  5     select 3 from dual
  6    ),
  7  tab2 (id) as
  8    (select 3 from dual union all
  9     select 4 from dual union all
 10     select 5 from dual
 11    )
 12  select id from tab1
 13  minus
 14  select id from tab2;

        ID
----------
         1
         2

SQL>

BTW, query you used (with a subquery) returns correct result; did you mean to say that you prefer NOT to use a subquery?

 <snip>
 12  select id from tab1
 13  where id not in (select id from tab2);

        ID
----------
         1
         2

CodePudding user response：

I tried this code and it worked fine :

select ID from Table1 where ID not in (select ID from Table2)

CodePudding user response：

You cant DROP rows from a table, but you can DELETE them.

So correcting you title to

How to use subquery to DELETE rows from Tab1 which are in Tab2 in Oracle SQL?

do so:

delete from tab1
where id  in (select id from tab2);

1 row deleted.

select * from tab1;

        ID
----------
         1
         2

Do not forget to commit to make the change permanent.