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’)
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
… [Trackback]
[…] Here you can find 27849 more Information to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] There you can find 59808 additional Info on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More here to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] There you can find 70043 additional Info on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More Info here on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Here you will find 332 more Info on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More Information here to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More Info here to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More here on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] There you will find 97221 additional Info to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Here you can find 62645 more Information on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More Info here to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More on on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Here you can find 12355 more Information to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Read More here to that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2014/08/in-clause-in-sql-server.html […]