CODEWITHSUNDEEP
oracle-databasejoinplsql
MattSom
MattSom

Reputation: 2377

Oracle select rows from a query which are not exist in another query

Let me explain the question.

I have two tables, which have 3 columns with same data tpyes. The 3 columns create a key/ID if you like, but the name of the columns are different in the tables. Now I am creating queries with these 3 columns for both tables. I've managed to independently get these results

For example:

SELECT ID, FirstColumn, sum(SecondColumn)
    FROM (SELECT ABC||DEF||GHI AS ID, FirstTable.* 
            FROM FirstTable
            WHERE ThirdColumn = *1st condition*)
GROUP BY ID, FirstColumn
;

SELECT ID, SomeColumn, sum(AnotherColumn)
    FROM (SELECT JKM||OPQ||RST AS ID, SecondTable.* 
            FROM SecondTable 
            WHERE AlsoSomeColumn  = *2nd condition*)
GROUP BY ID, SomeColumn
;

So I make a very similar queries for two different tables. I know the results have a certain number of same rows with the ID attribute, the one I've just created in the queries. I need to check which rows in the result are not in the other query's result and vice versa.

Do I have to make temporary tables or views from the queries? Maybe join the two tables in a specific way and only run one query on them?

As a beginner I don't have any experience how to use results as an input for the next query. I'm interested what is the cleanest, most elegant way to do this.

Upvotes: 2

Views: 688

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

No, you most probably don't need any "temporary" tables. WITH factoring clause would help.

Here's an example:

with 
first_query as
  (select id, first_column, ...
     from (select ABC||DEF||GHI as id, ...)
  ),
second_query as
  (select id, some_column, ...
     from (select JKM||OPQ||RST as id, ...)
  )
select id from first_query
minus
select id from second_query;

For another result you'd just switch the tables, e.g.

with ... <the same as above>
select id from second_query
minus
select id from first_query

Upvotes: 3

Related Questions