SQL IN Operator – Syntax and Examples

SQL IN Operator

The SQL IN operator is used to specify multiple values in a WHERE clause. It allows you to check if a value exists within a set of values.

The IN operator is an efficient alternative to using multiple OR conditions and is frequently used to filter results based on a specific list of values.

In this tutorial, we will go through SQL IN Operator, its syntax, and how to use this operator in SQL statements, with the help of well detailed examples.

---

Syntax of SQL IN Operator

The basic syntax of the SQL IN operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve.
• FROM: Specifies the table from which to retrieve data.
• WHERE column_name IN (value1, value2, …): Filters the rows where column_name matches any of the values specified in the parentheses.

---

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the IN operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert sample data into the students table:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

---

Examples: Using IN Operator in Queries

Now, let’s explore different scenarios of using the IN operator with this table.

---

1. Filtering by a List of Values

To select students who are in either the 8th or 9th grade:

SELECT *
FROM students
WHERE grade IN ('8th', '9th');

This query returns the records of students who are in the 8th or 9th grade, based on the values specified in the IN clause.

---

2. Filtering by Locality

To find students who live in “Northside” or “Westend”:

SELECT *
FROM students
WHERE locality IN ('Northside', 'Westend');

This query returns students whose locality is either “Northside” or “Westend”.

---

3. Using NOT IN to Exclude Values

To select students who are not in the 10th grade:

SELECT *
FROM students
WHERE grade NOT IN ('10th');

This query excludes students who are in the 10th grade, displaying only those in other grades.

Reference: SQL NOT Operator

---

4. Using IN with a Subquery

To select students who live in the same localities as students in the 9th grade:

SELECT name, locality
FROM students
WHERE locality IN (SELECT locality FROM students WHERE grade = '9th');

This query finds students who share the same locality as those in the 9th grade, using a subquery to fetch the list of matching localities.

---

FAQs for SQL IN

1. What does the SQL IN operator do?

The IN operator allows you to filter results by checking if a column’s value matches any value in a specified list.

2. Can IN be used with subqueries?

Yes, IN can be used with subqueries to filter results based on values returned by a subquery.

3. How is IN different from multiple OR conditions?

The IN operator is a more concise and efficient way of specifying multiple OR conditions. It is functionally equivalent to using multiple OR conditions but is easier to read and write.

4. Can IN be used with non-numeric data?

Yes, IN can be used with text, numeric, and date data types to filter results based on a list of values.

5. How do I exclude values with IN?

You can use NOT IN to exclude values from the results, returning rows where the column does not match any value in the list.