比较MSSQL中的表并获取不同的值

I have two tables tmp_1 and tmp_2 here, Table1

我在这里有两个表tmp_1和tmp_2，表1

------+---------
|name |lastname|
------+---------
|John |rosy   |
------+---------
|Demy |Sanches |
------+---------

Table2

表2

------+----------
|name2|lastname2|
------+----------
|John |rose     |
------+----------
|Demy |Sanches  |
------+----------

I am using Inner join to compare these two tables like,

我正在使用Inner join来比较这两个表，比如

select * from tmp_1 t1
inner join tmp_2 t2
on t1.name = t2.name
and t1.lastname = t2.lastname

OUTPUT:

OUTPUT：

name    lastname    name    lastname
Demy    Sanches     Demy    Sanches

Expected Output:

预期产出：

name    lastname    name    lastname
John    rosy        John    rose

which results in the values those are equal. But I would like to get values those differs from two tables. I have searched some stuff in *. But I could not get this done, Because of time constraints, I posted the question here to get help from you.

这导致值相等。但我想得到两个表不同的值。我在*中搜索过一些东西。但我无法完成这项任务，由于时间的限制，我在这里发布了一个问题，以便得到你的帮助。

Any help would be more useful to me. Thank you

任何帮助对我来说都会更有用。谢谢

3 个解决方案

#1

To find rows where the tables differ in just one column you can write the following

要在一列中查找表不同的行，您可以编写以下内容

select * from tmp_1 t1
inner join tmp_2 t2 on (t1.name = t2.name2 and t1.lastname != t2.lastname2) or 
                       (t1.name != t2.name2 and t1.lastname = t2.lastname2)

#2

Use the following may this works.This will work only for lastname mismatch.

使用以下可能是有效的。这只适用于姓氏不匹配。

select t1.name,t1.lastname,t2.name,t2.lastname from tmp_1 t1
inner join tmp_2 t2
on t1.name = t2.name
and t1.lastname != t2.lastname

If there a conditional selection with both field then

如果有两个字段的条件选择那么

select t1.name,t1.lastname,t2.name,t2.lastname from tmp_1 t1
inner join tmp_2 t2
on ((t1.lastname != t2.lastname and t1.name = t2.name) or 
(t1.lastname  = t2.lastname and t1.name != t2.name))

#3

If you simply want all records which don't have an exact match in the two tables, then the following will work. The main issue is that your question is not specific enough. However, logically, the following answer is correct, because it provides you with the complement of your original query (cf De Morgan).

如果您只是想要在两个表中没有完全匹配的所有记录，那么以下内容将起作用。主要问题是你的问题不够具体。但是，从逻辑上讲，以下答案是正确的，因为它为您提供了原始查询的补充（参见De Morgan）。

SELECT * FROM tmp_1 t1
    INNER JOIN tmp_2 t2 
        ON (t1.name != t2.name2 OR t1.lastname != t2.lastname2);

#1