SQL IN 操作符

SQL IN 操作符用来在SQL WHERE子句中多个值之间至少有一个匹配。多个值的序列可以是一个SQL SELECT查询的结果。

SQL IN 操作符语法

SELECT ColumnName1, ColumnName2, ColumnName3, ...
FROM 表名
WHERE ColumnName IN (Value1, Value2, Value3, ...)

或者

SELECT ColumnName1, ColumnName2, ColumnName3, ...
FROM 表1
WHERE ColumnName IN
          (
               SELECT ColumnName 
               FROM 表2
               WHERE prediction
          )

SQL IN 操作符范例

数据表: Employees

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 57000
206 Gecrrcc Srlkrt Finance 62000
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000
304 Tyoysww Osk Development 49000

例1
选择FirstName是Mazojys,Jssme 或Tyoysww的职员(Employee):

SELECT *
FROM Employees
WHERE FirstName IN ('Mazojys', 'Jssme', 'Tyoysww')

结果会类似于:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
302 Jssme Bdnaa Development 75000
304 Tyoysww Osk Development 49000

例2
我们有另外一个表EmpProject,对应Employees和Projects。
数据表: EmpProject

EmployeeId ProjectId
302 0032
303 0032
304 0034

我们想选择所有参与Project 0032的职员(Employee):

SELECT *
FROM Employees
WHERE EmployeeId IN 
          (
               SELECT EmployeeId
               FROM EmpProject 
               WHERE ProjectId = '0032'
          )

结果会类似于:

EmployeeId FirstName LastName Department Salary
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000