For example, in case of "IN", one can use the subquery to get the multiple values in the IN clause or we can have predefined values.

In this article, we are going to use the following table for some examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Salary] [int] NULL
)

Suppose, we have another table Empprojects, which contains the employees and their project relationship and it has the below table structure

create table Empprojects (empprojectid int identity(1,1), empnumber nvarchar (50), projectcode nvarchar(50))

Now suppose we want to get the records for the employees which are associated with the project having code "Proj002" or "Proj005", then we can use the following query:-

select * from employee where empnumber in (select empnumber from Empprojects where projectcode='Proj002' or projectcode='Proj005')

In the above query, we use the subquery "select empnumber from Empprojects where projectcode='Proj002' or projectcode='Proj005' " to fetch the multiple values in the IN clause.

Also suppose, we want to get the records of employee who worked in the first project having code 'Proj001'. We can use subquery for it as we see in the above example. But we know that that project is completed years back and only 3 employees having empnumber "A001" , "A002" and "A003" worked on it , then we can pass the empnumber of these 3 employee as predefined values in the IN clause as shown in the below query:-

select * from employee where empnumber In ('A001','A002','A003')