SQL 从表中选择值，其中排除另一个表中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8768254/
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
select values from a table where exclude values in another table
提问by Ramprasad
I am using PostgreSQL Database. I want to get a column values from a table by excluding the values exists in another table.
我正在使用 PostgreSQL 数据库。我想通过排除另一个表中存在的值来从表中获取列值。
select id from mytable where exclude(select id from another table)
In First table available id:
在第一个可用的表中：
101,102,103,104,105
101,102,103,104,105
In second table available id:
在第二个表中可用的 id：
101,104
101,104
I want result:
我想要结果：
102,103,105 (excluded values exist in second table)
How write Query for this?
如何为此编写查询？
回答by
Try
尝试
select id
from mytable
where id not in (select id from another_table);
or
或者
select id
from mytable
except
select id
from another_table;
回答by Frank Heikens
Using a LEFT JOIN an IS NULL is also an option:
使用 LEFT JOIN IS NULL 也是一种选择：
SELECT
id
FROM
mytable
LEFT JOIN another_table ON mytable.id = another_table.id
WHERE
another_table.id IS NULL;
回答by Matt
I tried solution from "user554546" Not sure what was happening on my example but I had to Select Distinct since once I had two values in another_table then my table would show nonfiltered values twice.
我尝试了来自“user554546”的解决方案 不确定我的示例中发生了什么，但我必须选择 Distinct，因为一旦我在 another_table 中有两个值，那么我的表将显示两次未过滤的值。
So lets say another_table had
所以让我们说 another_table 有
|ID|
|03|
|04|
|06|
main_table had
main_table 有
|ID|
|01|
|02|
|03|
|04|
|05|
|06|
After doing the query main_table would show up the following
执行查询 main_table 后会显示以下内容
|ID|
|01|
|01|
|01|
|02|
|02|
|02|
|05|
|05|
|05|
Distinct seems to solve that but any ideas why was this happening?
Distinct 似乎解决了这个问题，但任何想法为什么会发生这种情况？
