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:

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

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

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

</>
Copy
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');