IN clause in SQL Server

With IN clause, we can specify multiple values in a WHERE clause.

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’)

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Basic Concepts, SQL Server and tagged , . Bookmark the permalink.

32 Responses to IN clause in SQL Server

  1. Pingback: Jerome Powell Speech Today Stock Market Economy Gold Silver

  2. Pingback: TorSearch onion

  3. Pingback: w88 thailand

  4. Pingback: vao fun88

  5. Pingback: ฟุตบอลไทย

  6. Pingback: http://cena-pozycjonowania.pl/bezpieczenstwo-wewnetrzne-polski-w-latach-1989-2013-wybrane-aspekty-1107.php

  7. Pingback: w88ok

  8. Pingback: havaianas

  9. Pingback: Sexy Baccarat

  10. Pingback: hair removal for men

  11. Pingback: Accountant in Pensacola Florida

  12. Pingback: dang ky tai khoan fun88

  13. Pingback: p897#https://serwispodrozniczy.pl/forum/rozne-f6/organizacja-wesela-t165.html#p897

  14. Pingback: Play baccarat online

  15. Pingback: Samsara Market

  16. Pingback: ebenin amini türkce dublaj izle

  17. Pingback: pharma market research companies in mumbai

  18. Pingback: pokermas99

  19. Pingback: BQEssentials, LLC

  20. Pingback: main qq

  21. Pingback: warnetqq

  22. Pingback: raja qq

  23. Pingback: link dang nhap m88

  24. Pingback: Top 10 Dentist in Columbia Maryland

  25. Pingback: Replica Watch

  26. Pingback: Cut food cravings. Killer Weight Loss

  27. Pingback: car door decals graphics

  28. Pingback: agen qiuqiu99

  29. Pingback: rajawali qq

  30. Pingback: klikdokter

  31. Pingback: Homepage

  32. Pingback: www.rajawaliqq1.site

Leave a Reply