SQL 如何连接两个表但只返回不匹配的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6613708/
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
How can I join two tables but only return rows that don't match?
提问by Breakthrough
I have two tables which look like this:
我有两个看起来像这样的表：
T1: ID | Date | Hour | Interval
T2: ID | Date | Hour
I basically need to join these tables when their IDs, dates, and hours match. However, I only want to return the results from table 1 that do notmatch up with the results in table 2.
我基本上需要在它们的 ID、日期和时间匹配时加入这些表。但是，我只想返回表 1 中与表 2 中的结果不匹配的结果。
I know this seems simple, but where I'm stuck is the fact that there are multiple rows in table 1 that match up with table 2 (there are multiple intervals for any given hour). I need to return all of these intervals so long as they do not fall within the same hour period in table 2.
我知道这看起来很简单，但我遇到的问题是表 1 中有多行与表 2 匹配（任何给定的小时都有多个间隔）。我需要返回所有这些间隔，只要它们不在表 2 中的同一小时内。
Example data:
示例数据：
T1: 1 | 1/1/2011 | 1 | 1
1 | 1/1/2011 | 1 | 2
1 | 1/1/2011 | 2 | 1
1 | 1/1/2011 | 2 | 2
T2: 1 | 1/1/2011 | 1
My expected result set for this would be the last two rows from T1. Can anyone point me on the right track?
我对此的预期结果集将是T1. 任何人都可以指出我在正确的轨道上吗？
回答by Joe Stefanelli
SELECT T1.*
FROM T1
WHERE NOT EXISTS(SELECT NULL
FROM T2
WHERE T1.ID = T2.ID
AND T1.Date = T2.Date
AND T1.Hour = T2.Hour)
It could also be done with a LEFT JOIN:
也可以通过以下方式完成LEFT JOIN：
SELECT T1.*
FROM T1
LEFT JOIN T2
ON T1.ID = T2.ID
AND T1.Date = T2.Date
AND T1.Hour = T2.Hour
WHERE T2.ID IS NULL
回答by Costi Ciudatu
Use a LEFT JOINand filter out the lines that have non-NULLT2 columns:
使用 aLEFT JOIN并过滤掉具有非NULLT2 列的行：
SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID
AND T1.Date = T2.Date AND T1.Hour = T2.Hour
WHERE T2.ID IS NULL
