SQL IN Operator Explained [10 Practical Examples]

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

Doctor Table

Bill Table

Laboratory Table

 

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 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 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 31^st 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 1^st February 2000 to 10^th February 2000

OUTPUT:

 

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:

 

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 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:

 

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 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 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 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:

 

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