SQL IN Operator Explained [10 Practical Examples]


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

Overview of SQL IN Operator

SQL IN is a SQL logical operator used to match the expression or column value with list of values separated by commas , The SQL IN operator allows us to specify list values in a WHERE clause, It returns 1 when the search value present within the range otherwise returns 0. IN Operator replaces a group of arguments using the = operator that are combined with an OR condition in SELECT, INSERT, UPDATE or DELETE statements

 

SQL IN Syntax

{WHERE | HAVING | {AND | OR}} expression | column_name IN ({comp_value1, comp_value2[, ...] );
Or
{WHERE | HAVING | {AND | OR}} expression | column_name IN ({subquery});

Here,

  • Expression | column_name: It is an expression or column name which value is going to match in the list
  • {WHERE | HAVING | {AND | OR}} value: SQL IN works with either the WHERE or the HAVING clause, to specified multiple conditions with WHERE and HAVING clause we can use AND or OR logical operators
  • comp_value1, comp_value2...| com_valueN: List of comparative values within the parentheses separated by commas
  • Subquery: It is a Select subquery that returns one or more values of a compatible data type of the main query

 

Key Points of SQL IN Operator

  • The IN operator can only replace the = logical operator. It cannot replace <, >, <=, >=, BETWEEN, or SQL LIKE. It will only find exact matches.
  • SQL IN operator ignored duplicate values in the list
  • The IN operator can be used anywhere any other operator is used including the WHERE clauses, HAVING clauses, IF statements, or join predicates – although they should be extremely rare in SQL join predicates
  • Comparison list values don’t have to be a hard-coded, comma-separated list
  • The IN operator requires a subsequent listing of values enclosed within parentheses
  • Just as BETWEEN represents a special case of the AND operator, the IN operator allows for a special case of the OR operator.

 

SQL IN Examples

Consider hospital database with four tables patient, doctor , laboratory and bill for performing practical example
Patient Table

patient_id name age gender address disease doctor_id
1 reema 23 female althan,Surat fever 21
2 kusum 50 female vadodara heart failure 22
3 carlin 43 male vapi infection 23
4 rahul 26 male navsari cancer 21
6 hansha 55 female vapi diabetes 22

Doctor Table

doctor_id name age gender address
21 asif 55 male baruch
22 dhawal 40 male Surat
23 krishna 39 female Surat
24 lissa 35 female Navsari
25 leeba 34 female baruch
26 vini 33 female Surat
27 Dhiren 32 male Navsari

Bill Table

bill_no patient_id doctor_id room_charge no_of_days
5005 1 340 500 4
5006 2 600 480 8
5008 3 800 340 3
5009 4 780 890 6
5010 3 400 1
5011 1 200 300 1
5012 2 600 110 2
5013 3 330 210 1
5014 1 230 340 2

Laboratory Table

lan_no patient_id doctor_id date amount
10 1 21 02-02-2000 4000
20 2 21 09-09-2001 300
30 3 22 03-03-2001 600
40 1 23 02-06-2002 800
50 4 21 05-07-2003 900
60 2 25 10-04-2004 550
70 4 22 03-04-2005 900

 

SQL IN with Numeric list of values

Example 1: Write SQL query to fetch patient laboratory details who had to pay the amount in the range of 500 to 1000:

SELECT patient.name, patient.disease, laboratory.lab_no, laboratory.date, laboratory.amount
FROM  patient LEFT OUTER JOIN
laboratory ON patient.patient_id = laboratory.patient_id
WHERE  (laboratory.amount IN (500, 600, 700, 800, 900, 1000))
  • In this query, SQL Left outer join is used to retrieve from two tables patient and laboratory
  • To specify the range condition of 500 to 1000 SQL In operator is applied with where clause

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

SQL IN with String list of values

Example 2 : Write SQL query to fetch patient records who are living in Surat and Vadodara city

SELECT patient_id, name, age, gender, address, disease, city FROM  patient
WHERE (city IN ('surat', 'vadodara'))

In the previous query, SQL IN operator is applied in column name city to compare with the list of city names separated by a comma in a small bracket

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

SQL IN with Date list of values

Example 3: Write SQL Query to display patient name with laboratory report details who reporting date is in between 1 to 10 date of January 2001 to 31st December 2001

SELECT patient.name, patient.disease, laboratory.lab_no, laboratory.date, laboratory.amount
FROM  patient LEFT OUTER JOIN
 laboratory ON patient.patient_id = laboratory.patient_id
WHERE (laboratory.date IN ('01-02-2000', '02-02-2000', '03-02-2000', '04-02-2000', '05-02-2000', '06-02-2000', '07-02-2000', '08-02-2000', '09-02-2000', '10-02-2000'))

In the above query SQL IN operator is applied on column name date of table laboratory to retrieve patient data who did laboratory test in between the date of 1st February 2000 to 10th February 2000

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

SQL IN with Subquery

Example 4: Write SQL Query to retrieve patient data who has been admitted for more than a day in hospital:

SELECT bill.bill_no, bill.doctor_charge, bill.room_charge, bill.no_of_days, patient.name, patient.age, patient.gender, patient.address, patient.disease FROM bill 
INNER JOIN patient ON bill.patient_id = patient.patient_id
WHERE  (bill.no_of_days IN (SELECT no_of_days FROM bill AS bill_1 WHERE   (no_of_days > 1)))
  • In this query, SQL IN is applied with subquery of the select statement which is retrieving no. of days list values from bill table which are more than 1
  • In outer query patient details and their billing details has been fetch by combining both tables using SQL INNER JOIN

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

Example 5: Write SQL query to display doctor details whose patient has visited laboratory for lab report

SELECT  laboratory.lab_no, laboratory.patient_id, laboratory.doctor_id, laboratory.date, laboratory.amount, doctor.doctor_id AS Expr1, doctor.name, doctor.age, doctor.gender, doctor.address
FROM  laboratory LEFT OUTER JOIN doctor ON laboratory.doctor_id = doctor.doctor_id
WHERE (doctor.doctor_id IN (SELECT  doctor_id
FROM  laboratory AS laboratory_1))
  • In the above query, SQL IN operator is used with a subquery to retrieve doctor details whose patient entry is in the laboratory table
  • The subquery returns the list of doctor ids from laboratory table and then outer select query with SQL IN compare doctor table doctor id with retrieval list of doctor ids of subquery

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

SQL IN with columns

All examples thus far we have seen one variable or column being compared to a list of hard-coded values, but it can be the other use of SQL IN operator to compare a value with a list of column name values

Example 6 : Display patient billing details where either doctor charges or the room charges having 340 value

SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM bill
WHERE  (340 IN (doctor_charge, room_charge))
  • In the previous query, the SQL IN operator compares the list of column values with one constant value 340
  • This query returns records from the bill table where both the column doctor charge or room charge is of 340

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

Multiple SQL IN in a single query

We can use SQL IN operator multiple times in a single SQL query to perform multiple comparisons on different column values, SQL logical operator (OR, AND, NOT).

Example 7 : Display patient billing details where either doctor charges having a value like 300 or the room charges having 340 value

SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days FROM     bill
WHERE  (340 IN (room_charge)) OR (300 IN (doctor_charge))
  • In the above query, two SQL IN operators were used to compare two different column values of room charge and doctor charge with a constant value of 340 and 300
  • Logical OR operator is applied in between two SQL IN operators so if either of the condition is satisfied the record will be displayed in the output

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

SQL IN with Update statement

Example 8 : modify doctor charges set to 250 where the charges are in the range of 150 to 200 in the bill details table of patient

UPDATE bill SET  doctor_charge = 250
WHERE  (doctor_charge IN (150, 160, 170, 180, 190, 200))
  • When we execute the above query, the one record of bill table is updated with modification in doctor charge column value with 250
  • SQL IN operator is used with SQL update statement to updating column value by compare list of values
  • To see the result of the above query, SQL select query statement is used to fetch all records of bills

 OUTPUT:

SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM     bill

SQL IN Operator Explained [10 Practical Examples]

 

SQL IN with Delete statement

Example 9: Write SQL query to remove patient laboratory record who has paid bill amount between 300 to 350

DELETE FROM laboratory	
WHERE (amount IN (300, 310, 320, 330, 340, 350))

In the above query of SQL delete statement, SQL IN operator is applied on amount column value to compare with the list of values 300 to 350

OUTPUT:

To see the result of the above query, SQL select statement is used on table laboratory

SELECT lab_no, patient_id, doctor_id, date, amount
FROM laboratory

SQL IN Operator Explained [10 Practical Examples]

 

SQL IN with HAVING Condition

SQL IN operator can also be used with SQL HAVING clause when the need to retrieve records by making group of rows using GROUP By clause

Example 10:  Write SQL query to fetch patient data with lab details based on the disease of age group 20 to 25

SELECT patient.disease, patient.name, patient.age, laboratory.lab_no, laboratory.date
FROM  patient LEFT OUTER JOIN
laboratory ON patient.patient_id = laboratory.patient_id
GROUP BY patient.disease, patient.name, patient.age, laboratory.lab_no, laboratory.date
HAVING (patient.age IN (20, 21, 22, 23, 24, 25))
  • In this query, SQL Group by clause is used to make a group of records based on diseases name, and left outer join is applied to join two tables patient and laboratory
  • SQL IN operator is applied in the conditional statement HAVING with the age list of 21 to 25 to be compared

OUTPUT:

SQL IN Operator Explained [10 Practical Examples]

 

Summary

In this article we learned about SQL IN operator with the practical examples, syntax and some key points. Although most people prefer to use in, using = any is equivalent to using the in operator. The subquery expression IN (subquery) is like the IN comparison operator except we use a subquery to provide the list of values to check against rather than having to manually provide one.

 

References

SQL IN Operator

 

Further Reading

SQL IN and NOT IN

 

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X