SQL IN Operator Explained with Examples

SQL IN Operator with Indian People Table

SQL IN Operator Explained

Table of Contents

Learn how to use the SQL IN operator with the Indian People table.

What is the SQL IN Operator?

The IN operator in SQL allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions, making the query cleaner and easier to read. It checks if a column’s value matches any of the values in a list.

Syntax

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

Step 1: Create the IndianPeople Table

Let’s create a table called IndianPeople to store data about people from India. Here’s how to define it:

CREATE TABLE IndianPeople (
    ID INT,
    Name VARCHAR(50),
    City VARCHAR(50),
    State VARCHAR(50)
);

-- Insert sample data into the table
INSERT INTO IndianPeople (ID, Name, City, State) VALUES (1, 'Amit', 'Mumbai', 'Maharashtra');
INSERT INTO IndianPeople (ID, Name, City, State) VALUES (2, 'Priya', 'Delhi', 'Delhi');
INSERT INTO IndianPeople (ID, Name, City, State) VALUES (3, 'Suresh', 'Bangalore', 'Karnataka');
INSERT INTO IndianPeople (ID, Name, City, State) VALUES (4, 'Neelam', 'Kolkata', 'West Bengal');
INSERT INTO IndianPeople (ID, Name, City, State) VALUES (5, 'Ravi', 'Chennai', 'Tamil Nadu');
INSERT INTO IndianPeople (ID, Name, City, State) VALUES (6, 'Vikas', 'Pune', 'Maharashtra');
INSERT INTO IndianPeople (ID, Name, City, State) VALUES (7, 'Kiran', 'Hyderabad', 'Telangana');

Example 1: Using the IN Operator

We can use the IN operator to filter records by a list of values. Let’s say we want to get all people who live in either ‘Mumbai’, ‘Delhi’, or ‘Chennai’. The SQL query would look like this:

SELECT * FROM IndianPeople
WHERE City IN ('Mumbai', 'Delhi', 'Chennai');

Explanation: This query retrieves all records where the city is either ‘Mumbai’, ‘Delhi’, or ‘Chennai’.

Example Output:

ID	Name	City	State
1	Amit	Mumbai	Maharashtra
2	Priya	Delhi	Delhi
5	Ravi	Chennai	Tamil Nadu

Example 2: Using the NOT IN Operator

Now let’s find all the people who do NOT live in ‘Mumbai’, ‘Delhi’, or ‘Chennai’. The SQL query would be:

SELECT * FROM IndianPeople
WHERE City NOT IN ('Mumbai', 'Delhi', 'Chennai');

Explanation: This query retrieves all records where the city is NOT ‘Mumbai’, ‘Delhi’, or ‘Chennai’.