WHERE col1,col2 IN (...) [使用复合主键的 SQL 子查询]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4622453/
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
WHERE col1,col2 IN (...) [SQL subquery using composite primary key]
提问by Phrogz
Given a table foowith a composite primary key (a,b), is there a legal syntax for writing a query such as:
给定一个foo带有复合主键的表(a,b)，是否有用于编写查询的合法语法，例如：
SELECT ... FROM foo WHERE a,b IN (SELECT ...many tuples of a/b values...);
UPDATE foo SET ... WHERE a,b IN (SELECT ...many tuples of a/b values...);
If this is not possible, and you could not modify the schema, how could you perform the equivalent of the above?
如果这是不可能的，并且您无法修改架构，那么您如何执行上述等效操作？
I'm also going to put the terms "compound primary key", "subselect", "sub-select", and "sub-query" here for search hits on these aliases.
我还将在此处放置术语“复合主键”、“子选择”、“子选择”和“子查询”，以搜索这些别名的搜索结果。
Edit: I'm interested in answers for standard SQL as well as those that would work with PostgreSQL and SQLite 3.
编辑：我对标准 SQL 以及适用于 PostgreSQL 和 SQLite 3 的答案感兴趣。
采纳答案by Doug Currie
sqlite> create table foo (a,b,c);
sqlite> create table bar (x,y);
sqlite> select * from foo where exists (select 1 from bar where foo.a = bar.x and foo.b = bar.y);
Replace the select 1 from barwith your select ... many tuples of a/b values ....
替换为select 1 from bar您的select ... many tuples of a/b values ....
Or create a temporary table of your select ... many tuples of a/b values ...and use it in place of bar..
或者创建一个临时表select ... many tuples of a/b values ...并用它代替bar..
回答by onedaywhen
Your syntax is very close to Standard SQL!
您的语法非常接近标准 SQL！
The following is valid FULL SQL-92 (as confirmed by the Mimer SQL-92 Validator)
以下是有效的 FULL SQL-92（由Mimer SQL-92 Validator确认）
SELECT *
FROM foo
WHERE (a, b) IN (
SELECT a, b
FROM bar
);
Of course, not every SQL product supports full SQL-92 (shame!) If anyone would like to see this syntax supported in Microsoft SQL Server, they can vote for it here.
当然，并不是每个 SQL 产品都支持完整的 SQL-92（真可惜！）如果有人想看到 Microsoft SQL Server 支持这种语法，他们可以在这里投票。
A further SQL-92 construct that is more widely supported (e.g. by Microsoft SQL Server and Oracle) is INTERSECTe.g.
更广泛支持（例如 Microsoft SQL Server 和 Oracle）的另一个 SQL-92 构造是INTERSECT例如
SELECT a, b
FROM Foo
INTERSECT
SELECT a, b
FROM Bar;
Note that these constructs properly handle the NULLvalue, unlike some of the other suggestions here e.g. those using EXISTS (<equality predicates>), concatenated values, etc.
请注意，这些构造正确地处理了NULL值，这与此处的其他一些建议不同，例如使用EXISTS (<equality predicates>)、连接值等的建议。
回答by Ostin
You've done one very little mistake. You have to put a,b in parentheses.
你犯了一个很小的错误。你必须把 a,b 放在括号里。
SELECT ... FROM foo WHERE (a,b) IN (SELECT f,d FROM ...);
That works!
那个有效！
回答by Larry Lustig
The IN syntax you suggested is not valid SQL. A solution using EXISTS should work across all reasonably compliant SQL RDBMSes:
您建议的 IN 语法不是有效的 SQL。使用 EXISTS 的解决方案应该适用于所有合理兼容的 SQL RDBMS：
UPDATE foo SET x = y WHERE EXISTS
(SELECT * FROM bar WHERE bar.c1 = foo.c1 AND bar.c2 = foo.c2)
Be aware that this is often not especially performant.
请注意，这通常不是特别有效。
回答by Stefan H
SELECT ...
FROM foo
INNER JOIN (SELECT ...many tuples of a/b values...) AS results
ON results.a = foo.a
AND results.b = foo.b
That what you are looking for?
那是你在找什么？
回答by Toto
With concatenation, this works with PostgreSQL:
通过串联，这适用于 PostgreSQL：
SELECT a,b FROM foo WHERE a||b IN (SELECT a||b FROM bar WHERE condition);
UPDATE foo SET x=y WHERE a||b IN (SELECT a||b FROM bar WHERE condition);
回答by mobeetsatwork
If you need a solution that doesn't require the tuples of values already existing in a table, you can concatenate the relevant table values and items in your list and then use the 'IN' command.
如果您需要一个不需要表中已经存在的值元组的解决方案，您可以连接相关的表值和列表中的项目，然后使用“IN”命令。
In postgres this would look like this:
在 postgres 中，这看起来像这样：
SELECT * FROM foo WHERE a || '_' || b in ('Hi_there', 'Me_here', 'Test_test');
SELECT * FROM foo WHERE a || '_' || b in ('Hi_there', 'Me_here', 'Test_test');
While in SQL I'd imagine it might look something like this:
在 SQL 中，我想它可能看起来像这样：
SELECT * FROM foo WHERE CONCAT(a, "_", b) in ('Hi_there', 'Me_here', 'Test_test');
SELECT * FROM foo WHERE CONCAT(a, "_", b) in ('Hi_there', 'Me_here', 'Test_test');
回答by muaddibx
Firebird uses this concatenation formula:
Firebird 使用这个串联公式：
SELECT a,b FROM foo WHERE a||b IN (SELECT a||b FROM bar WHERE condition);
SELECT a,b FROM foo WHERE a||b IN (SELECT a||b FROM bar WHERE 条件);
回答by Simon
JOINSand INTERSECTSwork fine as a substitute for IN, but they're not so obvious as a substitute for NOT IN, e.g.: inserting rows from TableAinto TableBwhere they don't already exist in TableBwhere the PKon both tables is a composite.
JOINS和INTERSECTS做工精细的替代品IN，但他们不是作为一个替代那么明显NOT IN，例如：从插入行TableA到TableB他们这里尚未存在TableB在PK这两个表是一种复合材料。
I am currently using the concatenation method above in SQL Server, but it's not a very elegant solution.
我目前在 SQL Server 中使用上面的连接方法，但这不是一个非常优雅的解决方案。
