比较oracle sql中两个不同表中的两列是否存在差异

Hi first time posting for SQL,

您好第一次发布SQL，

I need to compare two different columns from two different tables in SQL.

我需要比较SQL中两个不同表的两个不同列。

For example there is two tables and each have one column in them and I need to compare them.

例如，有两个表，每个表中都有一列，我需要比较它们。

Column1 is in table1 and column2 is in table2.

Column1在table1中，column2在table2中。

I need to compare column1 and column2 for differences, I have looked online but was unable to find a query that would be able to help.

我需要比较column1和column2的差异，我已经在线查看但无法找到能够提供帮助的查询。

Essentially I need to find the inconsistencies in the two columns from two different tables.

基本上我需要从两个不同的表中找到两列中的不一致。

3 个解决方案

#1

The following Oracle SQL would be a solution provided you have an id that you can join on between the two tables.

如果您具有可以在两个表之间连接的ID，则以下Oracle SQL将是一种解决方案。

 select tab1.id,
           case
            when tab1.col1 > tab2.col1 OR tab1.col1 < tab2.col1 then 'Different'
            else 'Equals'
           end
    from tab1, tab2
    where tab1.id = tab2.id;

Test data SQL:

测试数据SQL：

create table tab1 ( id number, col1 varchar2(20));
create table tab2 ( id number, col1 varchar2(20));
insert into tab1 values (1, 'ABCD');
insert into tab1 values (2, 'EFGH');
insert into tab1 values (3, 'WXYZ');
insert into tab2 values (1, 'ABCD');
insert into tab2 values (2, 'EFG');
insert into tab2 values (3, 'ZYXW');
commit;

Results:

结果：

    ID CASEWHENT

     1 Equals
     2 Different
     3 Different

#2

Try this:

尝试这个：

    WITH x AS (SELECT 1 AS ID, 'THE QUICK BROWN' AS tab1 FROM dual UNION ALL
                   SELECT 2 AS ID, 'FOX JUMPS OVER' AS tab1  FROM dual),
         y AS (SELECT 1 AS ID, 'THE QUICK BROWN FOX' AS tab2 FROM DUAL UNION ALL
                  SELECT 2 AS ID, 'FOX JUMPS OVER' AS TAB2 FROM DUAL)

SELECT   X.ID,X.tab1,Y.tab2,(CASE WHEN (X.tab1 = tab2) 
                             THEN 'Tab1 is equal  Tab2'
                             ELSE 'Tab1 is not equal to Tab2' END) AS Remarks
FROM X INNER JOIN Y ON X.ID = Y.ID;

OUTPUT:

OUTPUT：

    ID       TAB1                 TAB2                     REMARKS   
    1  THE QUICK BROWN      THE QUICK BROWN FOX     Tab1 is not equal to Tab2 
    2  FOX JUMPS OVER       FOX JUMPS OVER          Tab1 is equal  Tab2

#3

You can do inner join and then find the difference.

你可以做内连接，然后找到差异。

SELECT
       table1.column1,
       table2.column2, 
      (CASE WHEN (table1.column1 = table2.column2 THEN 'No change' ELSE 'Change DETECTED' END)) AS Difference
FROM
table1 INNER JOIN table2 ON table1.id=table2.id

#1