SQL 选择 NOT IN 多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8033604/
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 NOT IN multiple columns
提问by Gunjan Nigam
I need to implement the following query
我需要实现以下查询
SELECT *
FROM friend
WHERE ( friend.id1, friend.id2 )
NOT IN (SELECT id1,
id2
FROM likes)
but NOT IN can't be implemented on multiple columns. How do I write this query
但 NOT IN 不能在多列上实现。我如何编写此查询
回答by Micha? Powaga
I'm not sure whether you think about:
我不确定你是否考虑过：
select * from friend f
where not exists (
select 1 from likes l where f.id1 = l.id and f.id2 = l.id2
)
it works only if id1 is related with id1 and id2 with id2 not both.
它仅在 id1 与 id1 和 id2 与 id2 相关，而不是两者都相关时才有效。
回答by Erwin Brandstetter
Another mysteriously unknown RDBMS. Your Syntax is perfectly fine in PostgreSQL. Other query styles may perform faster (especially the NOT EXISTSvariant or a LEFT JOIN), but your query is perfectly legit.
另一个神秘未知的 RDBMS。您的语法在 PostgreSQL 中非常好。其他查询样式可能执行得更快（尤其是NOT EXISTS变体或 a LEFT JOIN），但您的查询完全合法。
Be aware of pitfalls with NOT IN, though, when involving any NULLvalues:
NOT IN但是，在涉及任何NULL值时，请注意 的陷阱：
Variant with LEFT JOIN:
带有左连接的变体：
SELECT *
FROM friend f
LEFT JOIN likes l USING (id1, id2)
WHERE l.id1 IS NULL;
See @Micha?'s answer for the NOT EXISTSvariant.
A more detailed assessment of four basic variants:
请参阅@Micha? 对NOT EXISTS变体的回答。
对四种基本变体的更详细评估：
回答by vacolane
I use a way that may look stupid but it works for me. I simply concat the columns I want to compare and use NOT IN:
我使用了一种可能看起来很愚蠢但对我有用的方法。我只是连接要比较的列并使用 NOT IN：
SELECT *
FROM table1 t1
WHERE CONCAT(t1.first_name,t1.last_name) NOT IN (SELECT CONCAT(t2.first_name,t2.last_name) FROM table2 t2)
回答by Raoul George
You should probably use NOT EXISTSfor multiple columns.
您可能应该NOT EXISTS用于多列。
