CODEWITHSUNDEEP
sqloracle-database
dingaro
dingaro

Reputation: 2342

How to use MINUS before WITH statement so as to select all rows from table 1 which are not in table 2 in Oracle SQL?

I have a following query in Oracle SQL Developer:

query1:
with logg as
(select ID ...)
UNION
select ID from table2

query2:
select ID from table3

I would like to take all IDs from query 2 but remove those from query 2 which are in query 1, so for example:

results of query2

1, 2, 3

results of query1:

3, 4, 5

My expected result would be:

1 , 2  -- because they are in query 1 but not in query2.

I tried to use:

select ID from table3
minus
    (
        with logg as
        (select ID ...)
        UNION
        select ID from table2
    )

But it generates error: cannot recognize input near 'MINUS' 'with' 'logg' in set operator so as I mean it is no possible to use "MINUS" before "with".

What can I do ?

Upvotes: 1

Views: 3231

Answers (3)

user5683823
user5683823

Reputation:

Here is one way.

Suppose you could write the three queries like this:

1.

select id from ... where ...

select id from table2

select id from table3

Now, you want to get all the distinct id's from 1. and 2., which are not found in 3. Correct?

Then you can do something like this:

with
  logg (id) as (
    select id from ... where ... 
    UNION
    select id from table2
  )
select id
from   logg
where  id not in (select id from table3 where id is not null)
;

The with clause is what you have already (I think). To exclude the id's from the last query, you can use the not in condition.

Note the where id is not null clause in the subquery at the end. If id can't be null, you don't need it (obviously), but it won't hurt anyway. But if id can be null, you must include that where condition; this is where most beginners get the not in condition wrong.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48850

You can compose multiple CTEs (Common Table Expressions) in a WITH clause, by separating them with commas.

For example:

with
logg as ( -- first CTE
  select ID ...
),
u as ( -- second CTE
  select * from logg
  UNION
  select ID from table2
)
-- now, the main query
select * from u
minus
select ID from table3

Upvotes: 2

pmdba
pmdba

Reputation: 7043

Try this, without the "with" clause:

select ID from table3
minus
    (
        (select ID ...)
        UNION
        select ID from table2
    )

Upvotes: 0

Related Questions