IN 子句中的 SQL 多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13027708/
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 multiple columns in IN clause
提问by Htaras
If we need to query a table based on some set of values for a given column, we can simply use the IN clause.
如果我们需要根据给定列的某些值集查询表，我们可以简单地使用 IN 子句。
But if query need to be performed based on multiple columns, we could not use IN clause(grepped in SO threads.)
但是如果需要基于多列执行查询，我们不能使用 IN 子句（在 SO 线程中 grepped）。
From other SO threads, we can circumvent this problem using joins or exists clause etc. But they all work if both main table and search data are in the database.
从其他 SO 线程，我们可以使用连接或存在子句等来规避这个问题。但是如果主表和搜索数据都在数据库中，它们都可以工作。
E.g
User table:
firstName, lastName, City
Given a list of (firstname, lastName) tuples, I need to get the cities.
给定一个 (firstname, lastName) 元组列表，我需要获取城市。
I can think of following solutions.
我可以想到以下解决方案。
1
1
Construct a select query like,
构建一个选择查询，如，
SELECT city from user where (firstName=x and lastName=y) or (firstName=a and lastName=b) or .....
2
2
Upload all firstName, lastName values into a staging table and perform a join between 'user' table and the new staging table.
将所有 firstName、lastName 值上传到临时表中，并在“用户”表和新临时表之间执行连接。
Are there any options for solving this problem and what is the preferred of solving this problem in general?
是否有解决此问题的任何选项？一般而言，解决此问题的首选方法是什么？
回答by xdazz
You could do like this:
你可以这样做：
SELECT city FROM user WHERE (firstName, lastName) IN (('a', 'b'), ('c', 'd'));
回答by dan1111
It often ends up being easier to load your data into the database, even if it is only to run a quick query. Hard-coded data seems quick to enter, but it quickly becomes a pain if you start having to make changes.
将数据加载到数据库中通常会更容易，即使只是为了运行快速查询。硬编码数据似乎很快就会输入，但如果您开始必须进行更改，它很快就会变得很痛苦。
However, if you want to code the names directly into your query, here is a cleaner way to do it:
但是，如果您想将名称直接编码到您的查询中，这里有一个更简洁的方法：
with names (fname,lname) as (
values
('John','Smith'),
('Mary','Jones')
)
select city from user
inner join names on
fname=firstName and
lname=lastName;
The advantage of this is that it separates your data out of the query somewhat.
这样做的好处是它在某种程度上将您的数据与查询分开。
(This is DB2 syntax; it may need a bit of tweaking on your system).
（这是 DB2 语法；它可能需要对您的系统进行一些调整）。
回答by grokster
In Oracle you can do this:
在 Oracle 中，您可以这样做：
SELECT * FROM table1 WHERE (col_a,col_b) IN (SELECT col_x,col_y FROM table2)
回答by Alexander Roskamp
In general you can easily write the Where-Condition like this:
一般来说，您可以轻松地编写 Where-Condition 如下：
select * from tab1
where (col1, col2) in (select col1, col2 from tab2)
Note
Oracle ignores rows where one or more of the selected columns is NULL. In these cases you probably want to make use of the NVL-Funktion to map NULL to a special value (that should not be in the values):
注意
Oracle 会忽略其中一个或多个选定列为 NULL 的行。在这些情况下，您可能希望使用NVL-Funktion 将 NULL 映射到特殊值（不应在值中）：
select * from tab1
where (col1, NVL(col2, '---') in (select col1, NVL(col2, '---') from tab2)
回答by Jaimal Chohan
Ensure you have an index on your firstname and lastname columns and go with 1. This really won't have much of a performance impact at all.
确保您的名字和姓氏列上有一个索引，并使用 1。这实际上根本不会对性能产生太大影响。
EDIT:After @Dems comment regarding spamming the plan cache ,a better solution might be to create a computed column on the existing table (or a separate view) which contained a concatenated Firstname + Lastname value, thus allowing you to execute a query such as
编辑：在@Dems 评论关于计划缓存的垃圾邮件之后，更好的解决方案可能是在现有表（或单独的视图）上创建一个计算列，其中包含一个连接的 Firstname + Lastname 值，从而允许您执行查询，例如
SELECT City
FROM User
WHERE Fullname in (@fullnames)
where @fullnameslooks a bit like "'JonDoe', 'JaneDoe'"etc
哪里@fullnames看起来有点像"'JonDoe', 'JaneDoe'"等
回答by Jirka Hanika
Determine whether the list of names is different with each query or reused. If it is reused, it belongs to the database.
确定名称列表是否与每个查询不同或重复使用。如果它被重用，它就属于数据库。
Even if it is unique with each query, it may be useful to load it to a temporary table (#tablesyntax) for performance reasons - in that case you will be able to avoid recompilation of a complex query.
即使它对于每个查询都是唯一的，#table出于性能原因将其加载到临时表（语法）可能很有用- 在这种情况下，您将能够避免重新编译复杂的查询。
If the maximum number of names is fixed, you should use a parametrized query.
如果名称的最大数量是固定的，则应使用参数化查询。
However, if none of the above cases applies, I would go with inlining the names in the query as in your approach #1.
但是，如果上述情况都不适用，我会像您的方法#1 一样在查询中内联名称。
