如何在Oracle SQL中获取两个查询的差异数据（Query A - Query B）

我有两个SELECT查询，Query A包含全部数据，Query B包含Query A中的部分数据，需要得到Query A中存在但Query B中不存在的记录。以下是具体示例：

Query A #

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

Query A 输出结果 #

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');

Query B 输出结果 #

预期输出 #

是否可以通过INNER JOIN/OUTER JOIN/EXCEPT/UNION或其他方式实现该需求？

当然可以，针对你的需求，以下几种方法都能实现Query A减去Query B的效果：

方法1：使用 NOT IN #

直接在Query A的WHERE条件中排除Query B返回的结果：

SELECT meaning 
FROM apps.fnd_lookup_values 
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP'
  AND meaning NOT IN (
    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')
  );

方法2：使用 LEFT JOIN + IS NULL #

将Query A和Query B通过关联字段左连接，筛选出Query B中无匹配的记录：

SELECT a.meaning
FROM (
    SELECT meaning 
    FROM apps.fnd_lookup_values 
    WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP'
) a
LEFT JOIN (
    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')
) b ON a.meaning = b.program
WHERE b.program IS NULL;

方法3：使用 MINUS（Oracle）/ EXCEPT（SQL Server/PostgreSQL） #

如果是Oracle数据库，用MINUS运算符直接取两个结果集的差集；其他数据库如SQL Server、PostgreSQL用EXCEPT：

-- Oracle 版本
SELECT meaning 
FROM apps.fnd_lookup_values 
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP'
MINUS
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');

-- SQL Server/PostgreSQL 版本
SELECT meaning 
FROM apps.fnd_lookup_values 
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP'
EXCEPT
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');

方法4：使用 NOT EXISTS #

通过子查询判断当前记录是否不存在于Query B的结果中：

SELECT meaning 
FROM apps.fnd_lookup_values a
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP'
  AND NOT EXISTS (
    SELECT 1
    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 = a.meaning
  );

注意事项 #

• NOT IN需要注意子查询中不能返回NULL值，否则会导致整个查询无结果；NOT EXISTS和LEFT JOIN则不受NULL影响。
• MINUS/EXCEPT会自动去重，如果你需要保留Query A中的重复记录（虽然你的Query A看起来没有重复），建议用其他方法。

内容的提问来源于stack exchange，提问作者Nikhil Chavan