MySQL SQL如何比较两个不同表中的两列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28886856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL how to compare two columns from two different tables
提问by mzbib
I have two tables, in which table 1 contains 4 columns while table 2 contains 8 columns. I have two columns in table1 that I want to compare them with two columns in table2.
我有两个表，其中表 1 包含 4 列，而表 2 包含 8 列。我在 table1 中有两列，我想将它们与 table2 中的两列进行比较。
Table 1 have column1 and column2 (that needs to be compared)
Table 2 have column6 and column7 (that needs to be compared)
I need to compare the combination of the two columns. I tried to do the below query however it doesn't work
我需要比较两列的组合。我试图做下面的查询，但它不起作用
Select * from table1
where column1, column2 NOT IN (Select column6, column7 from table2)
How can I compare the two columns in the the two tables?
如何比较两个表中的两列？
回答by Jared
Try a minus statement. This will give you any results from the first select statement of table1 that aren't in the second select statement on table2.
尝试减号语句。这将为您提供 table1 的第一个 select 语句中不在 table2 上的第二个 select 语句中的任何结果。
select column1, column2 from table1
minus
select column6, column7 from table2
回答by jarlh
NOT EXISTSis a "null safe" version of NOT IN.
If you mean the combination column1 AND column2 not in same row in table2:
NOT EXISTS是NOT IN. 如果您的意思是组合 column1 AND column2 不在 table2 的同一行中：
select *
from table1
where NOT EXISTS (select 1 from table2
where table1.column1 = table2.column6
and table1.column2 = table2.column7)
Or if you mean just column1 and column2 values can't even be in different rows in table2:
或者，如果您的意思只是 column1 和 column2 值甚至不能在 table2 中的不同行中：
select *
from table1
where NOT EXISTS (select 1 from table2
where table1.column1 = table2.column6)
and NOT EXISTS (select 1 from table2
where table1.column2 = table2.column7)
回答by Sam
Except shows the difference between two tables (the Oracle guys use minus instead of except and the syntax and use is the same). It is used to compare the differences between two tables. For example, let's see the differences between the two tables
除了显示两个表之间的差异（Oracle 人员使用减号而不是除了，语法和用法是相同的）。它用于比较两个表之间的差异。例如，让我们看看两个表之间的差异
SELECT * FROM
table1
EXCEPT
SELECT * FROM
table2
回答by Tolulope Olufohunsi
SELECT * FROM table1 t1
RIGHT JOIN table2 t2
WHERE
t1.c1 = t2.c6 AND
t1.c2 = t2.c7
回答by Maxqueue
select * from table1 where column1 not in(select column 6 from table2) or column2 not in(select column7 from table2)
This will give you rows from table1 where there are differences between col1 and col6 or col2 and col7
这将为您提供 table1 中的行，其中 col1 和 col6 或 col2 和 col7 之间存在差异
Hope this helps
希望这可以帮助
回答by juergen d
The query with the least comparisions I can think of is
我能想到的最少比较的查询是
Select t1.*
from table1 t1
left join table2 t2 on t1.column1 in (t2.column6, t2.column7)
or t1.column2 in (t2.column6, t2.column7)
where t2.column6 is null
