sqloracle-databaseleft-joininner-joinouter-join

How to fetch uncommon data from two different tables in Oracle SQL (query A - query B)


I have two select queries, where query A has all the data and I have another query with some data. We need to fetch the uncommon data from both query records. Let's say (query A - query B).

Please find below example with existing data and expected output.

Query A:

SELECT meaning 
FROM apps.fnd_lookup_values 
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP';

Output of Query A:

Meaning
Autoinvoice Import Program
Purge Concurrent Request and/or Manager Data
Create Intercompany AP Invoices
Rollup Cumulative Lead Times
Record Order Management Transactions

Query B:

SELECT DISTINCT fcs.program
FROM apps.fnd_concurrent_requests fcr,
     apps.fnd_concurrent_programs_tl fcp,
     apps.fnd_responsibility_tl frl,
     apps.fnd_user fu,
     apps.fnd_conc_req_summary_v fcs
WHERE fcr.phase_code = 'P'
  AND fcr.request_id = fcs.request_id
  AND frl.language = 'US'
  AND fcr.requested_by = fu.user_id
  AND fcr.responsibility_id = frl.responsibility_id
  AND fcr.status_code IN ('P','Q')
  AND fcp.language = 'US'
  AND fcp.source_lang = 'US'
  AND fcr.concurrent_program_id = fcp.concurrent_program_id
  AND fcr.requested_start_date >= SYSDATE
  AND fcs.program IN (SELECT meaning FROM apps.fnd_lookup_values 
                      WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP');

Output of query B:

Program
Autoinvoice Import Program
Create Intercompany AP Invoices
Record Order Management Transactions

Below is the final expected output (fetch the uncommon data from both queries - query A which will have all the data will be minus from query B records):

Meaning
Purge Concurrent Request and/or Manager Data
Rollup Cumulative Lead Times

Can it be achieved from INNER JOIN/OUTER JOIN/EXPECT/UNION or anything else?


Solution

  • "query A - query B" is actually a great way of describing it, as Oracle has a minus set operator:

    SELECT meaning -- This is query A
    FROM apps.fnd_lookup_values 
    WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP';
    MINUS -- Set operator here
    SELECT fcs.program -- This is query B, with the DISTINCT modifier removed
    FROM apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs_tl fcp,
         apps.fnd_responsibility_tl frl,
         apps.fnd_user fu,
         apps.fnd_conc_req_summary_v fcs
    WHERE fcr.phase_code = 'P'
      AND fcr.request_id = fcs.request_id
      AND frl.language = 'US'
      AND fcr.requested_by = fu.user_id
      AND fcr.responsibility_id = frl.responsibility_id
      AND fcr.status_code IN ('P','Q')
      AND fcp.language = 'US'
      AND fcp.source_lang = 'US'
      AND fcr.concurrent_program_id = fcp.concurrent_program_id
      AND fcr.requested_start_date >= SYSDATE
      AND fcs.program IN (SELECT meaning FROM apps.fnd_lookup_values 
                          WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP');