Oracle 11g中对比两表指定字段差异的SQL语句失效排查

使用Oracle 11g，现有两张数据表，表结构如下：

CREATE TABLE  "WP_CLI_CARD_JF" (
  "COM_MD_CODE" VARCHAR2(6 BYTE) NOT NULL,
  "COM_DB_ID" VARCHAR2(5 BYTE) NOT NULL,
  "CLI_CARD_JF_LIST_NO" VARCHAR2(20 BYTE) NOT NULL,
  "CLI_CARD" VARCHAR2(10 BYTE) DEFAULT ' ' NOT NULL,
  "CLI_CARD_JF_TYPE" VARCHAR2(2 BYTE) NOT NULL,
  "CLI_CARD_JF_NUM" NUMBER(12,3) DEFAULT NULL,
  "CLI_CARD_JF_DATE" DATE DEFAULT NULL,
  "CLI_CARD_JF_MEMO" VARCHAR2(50 BYTE) DEFAULT NULL,
  "CUR_NO" VARCHAR2(2 BYTE) DEFAULT NULL,
  "CLI_CARD_JF_ZT" VARCHAR2(2 BYTE) DEFAULT '1' NOT NULL,
  "PROD_NO" VARCHAR2(8 BYTE) NOT NULL,
  "PROD_ADD" VARCHAR2(50 BYTE) NOT NULL,
  "BATCH_NO" VARCHAR2(12 BYTE) NOT NULL,
  "PROD_NUM" NUMBER(12,3) DEFAULT NULL,
  "SELL_PRICE" NUMBER(10,2) DEFAULT NULL,
  "STD_PRICE" NUMBER(10,2) DEFAULT NULL,
  "CLI_CARD_JF_DATE_UP" DATE DEFAULT sysdate,
  "UPLOADEDBYWEBAPI" CHAR(1 BYTE) DEFAULT NULL
);

CREATE TABLE  "YW_JFXFJLB" (
  "ID" NUMBER(9,0) NOT NULL,
  "HYKH" VARCHAR2(255 BYTE),
  "HYSJH" VARCHAR2(255 BYTE),
  "HYMC" VARCHAR2(255 BYTE),
  "RETURNCODE" NUMBER(9,0),
  "CURRINDEX" NUMBER(9,0),
  "PAGESCOUNT" NUMBER(9,0),
  "TOTALRECORDSCOUNT" NUMBER(9,0),
  "TOTALRECORDS" NUMBER(9,0),
  "COMDBID" VARCHAR2(255 BYTE),
  "CLICARDJFLISTNO" VARCHAR2(255 BYTE),
  "CLICARD" VARCHAR2(255 BYTE),
  "CLICARDJFTYPE" VARCHAR2(255 BYTE),
  "CLICARDJFNUM" NUMBER(20,8),
  "CLICARDJFDATE" VARCHAR2(255 BYTE),
  "CLICARDJFMEMO" VARCHAR2(255 BYTE),
  "CURNO" VARCHAR2(255 BYTE),
  "PRODNO" VARCHAR2(255 BYTE),
  "BATCHNO" VARCHAR2(255 BYTE),
  "PRODADD" VARCHAR2(255 BYTE),
  "PRODNAME" VARCHAR2(255 BYTE),
  "MONAD" VARCHAR2(255 BYTE),
  "PRODPZWH" VARCHAR2(255 BYTE),
  "PRODMEMO" VARCHAR2(255 BYTE),
  "PRODSIZE" VARCHAR2(255 BYTE),
  "PRODNUM" NUMBER(20,8),
  "SELLPRICE" NUMBER(20,8),
  "STDPRICE" NUMBER(20,8),
  "CLICARDJFDATEUP" VARCHAR2(255 BYTE),
  "COMMDCODE" VARCHAR2(255 BYTE),
  "COMJCNAME" VARCHAR2(255 BYTE),
  "COMZIPNAME" VARCHAR2(255 BYTE),
  "BEIZHU" VARCHAR2(255 BYTE),
  "NAME" VARCHAR2(255 BYTE),
  "CREATEBY" NUMBER(9,0),
  "CREATED" DATE DEFAULT SYSDATE,
  "UPDATED" DATE DEFAULT SYSDATE,
  "UPDATEBY" NUMBER(9,0),
  "ORGID" NUMBER(9,0),
  "NOTE" VARCHAR2(255 BYTE)
);

已向两张表各插入一条记录：

INSERT INTO  "YW_JFXFJLB" VALUES ('3110632', NULL, NULL, NULL, '0', '16', '25', '484', '0', '01', '2016120501005', '9000000003', '1', '70', '20161205150855', NULL, '01', 'H0110050', '3344', 'loreal', NULL, NULL, NULL, NULL, NULL, '1', '70', '70', '20161205150855', '800000', 'hq', 'for test', NULL, NULL, '0', TO_DATE('2024-07-05 09:20:06', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-07-05 09:20:06', 'SYYYY-MM-DD HH24:MI:SS'), '0', '0', NULL);

INSERT INTO wp_cli_card_jf  (com_md_code, com_db_id, cli_card_jf_list_no, cli_card, cli_card_jf_type, cli_card_jf_num, cli_card_jf_date, cli_card_jf_memo, cur_no, cli_card_jf_zt, prod_no, prod_add, batch_no, prod_num, sell_price, std_price, cli_card_jf_date_up, uploadedbywebapi) VALUES ('800000', '01', '2016120501005', '9000000003', '1', 70.000, TO_DATE('2016-12-05 15:03:40', 'YYYY-MM-DD HH24:MI:SS'), '', '01', '1', 'H0110050', 'loreal', '3344', 1.000, 70.00, 70.00, TO_DATE('2016-12-05 15:08:55', 'YYYY-MM-DD HH24:MI:SS'), NULL);

想要基于com_md_code、prod_no、batch_no、prod_add和cli_card_jf_memo这些关键字段找出两表间的差异，使用了如下SQL语句：

SELECT b.*
FROM Yw_jfxfjlb b
LEFT JOIN WP_CLI_CARD_JF a
ON a.com_md_code = b.commdcode 
AND NVL(trim(a.cli_card_jf_list_no),'') = NVL(trim(b.clicardjflistno),'')
AND NVL(trim(a.prod_no),'') = NVL(trim(b.prodno),'')
AND NVL(trim(a.batch_no), '') = NVL(trim(b.batchno), '')
AND NVL(trim(a.prod_add), '') = NVL(trim(b.prodadd), '')
AND NVL(trim(a.CLI_CARD_JF_MEMO), '') = NVL(trim(b.CLICARDJFMEMO), '')
WHERE a.com_md_code IS NULL and  b.CLICARD='9000000003';

期望该语句能展示上述字段值不一致的记录，但实际结果与预期相反，请问SQL语句存在什么错误？

核心逻辑错误 #

你的SQL当前实现的是查找仅存在于Yw_jfxfjlb、且在WP_CLI_CARD_JF中找不到所有ON条件完全匹配记录的数据，而非字段值不一致的记录。

你插入的两条测试记录，所有ON条件中的字段值都是匹配的，因此LEFT JOIN后a.com_md_code不会为NULL，查询结果为空，和你预期的相反。

额外匹配条件冗余 #

你明确说明要比较的字段是com_md_code、prod_no、batch_no、prod_add和cli_card_jf_memo，但ON条件中额外加入了cli_card_jf_list_no的匹配判断。这会导致即使其他目标字段存在差异，只要该字段匹配，记录就会被JOIN上，无法被检测到差异。

正确的差异查询方案 #

要找出两表中目标字段的差异，需要覆盖三种场景：

1. 同基准键（比如cli_card）下，目标字段值不一致的记录
2. 仅存在于Yw_jfxfjlb的记录
3. 仅存在于WP_CLI_CARD_JF的记录

以下是实现该逻辑的SQL：

-- 1. 同卡号下目标字段存在差异的记录
SELECT b.*
FROM Yw_jfxfjlb b
JOIN WP_CLI_CARD_JF a
ON a.cli_card = b.clicard
WHERE b.CLICARD='9000000003'
AND (
    NVL(trim(a.com_md_code),'') <> NVL(trim(b.commdcode),'')
    OR NVL(trim(a.prod_no),'') <> NVL(trim(b.prodno),'')
    OR NVL(trim(a.batch_no), '') <> NVL(trim(b.batchno), '')
    OR NVL(trim(a.prod_add), '') <> NVL(trim(b.prodadd), '')
    OR NVL(trim(a.CLI_CARD_JF_MEMO), '') <> NVL(trim(b.CLICARDJFMEMO), '')
)
UNION ALL
-- 2. 仅存在于Yw_jfxfjlb的记录
SELECT b.*
FROM Yw_jfxfjlb b
LEFT JOIN WP_CLI_CARD_JF a
ON a.cli_card = b.clicard
WHERE b.CLICARD='9000000003'
AND a.cli_card IS NULL
UNION ALL
-- 3. 仅存在于WP_CLI_CARD_JF的记录（映射到Yw_jfxfjlb的表结构）
SELECT NULL AS ID, NULL AS HYKH, NULL AS HYSJH, NULL AS HYMC, NULL AS RETURNCODE,
       NULL AS CURRINDEX, NULL AS PAGESCOUNT, NULL AS TOTALRECORDSCOUNT, NULL AS TOTALRECORDS,
       a.COM_DB_ID AS COMDBID, a.CLI_CARD_JF_LIST_NO AS CLICARDJFLISTNO, a.CLI_CARD AS CLICARD,
       a.CLI_CARD_JF_TYPE AS CLICARDJFTYPE, a.CLI_CARD_JF_NUM AS CLICARDJFNUM,
       TO_CHAR(a.CLI_CARD_JF_DATE, 'YYYYMMDDHH24MISS') AS CLICARDJFDATE,
       a.CLI_CARD_JF_MEMO AS CLICARDJFMEMO, a.CUR_NO AS CURNO, a.PROD_NO AS PRODNO,
       a.BATCH_NO AS BATCHNO, a.PROD_ADD AS PRODADD, NULL AS PRODNAME, NULL AS MONAD,
       NULL AS PRODPZWH, NULL AS PRODMEMO, NULL AS PRODSIZE, a.PROD_NUM AS PRODNUM,
       a.SELL_PRICE AS SELLPRICE, a.STD_PRICE AS STDPRICE,
       TO_CHAR(a.CLI_CARD_JF_DATE_UP, 'YYYYMMDDHH24MISS') AS CLICARDJFDATEUP,
       a.COM_MD_CODE AS COMMDCODE, NULL AS COMJCNAME, NULL AS COMZIPNAME, NULL AS BEIZHU,
       NULL AS NAME, NULL AS CREATEBY, NULL AS CREATED, NULL AS UPDATED, NULL AS UPDATEBY,
       NULL AS ORGID, NULL AS NOTE
FROM WP_CLI_CARD_JF a
LEFT JOIN Yw_jfxfjlb b
ON a.cli_card = b.clicard
WHERE a.CLI_CARD='9000000003'
AND b.clicard IS NULL;

内容的提问来源于stack exchange，提问作者Tommas Lees