How to use the WHERE IN keyword in SQL
Overview
We use the IN operator with WHERE in SQL to filter based on many conditions.
We can specify multiple values in a WHERE clause using the IN operator. This makes it easier to specify multiple OR conditions.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
Example
Let’s assume we have a table called Person with the columns ID, name, age, state, and gender.
Now, we want to obtain the names and ages of all persons in the states of Lagos, Abuja, and Ibadan.
How do we get this information from our table?
The following code shows how to do this using the WHERE IN keyword in SQL.
CREATE TABLE Person (ID int,name varchar(100),age int,gender varchar(10),state varchar(15));-- Insert dataINSERT INTO PersonVALUES (1,'Sharon Peller','16','Female','Kogi');INSERT INTO PersonVALUES (2,'Paul Dons','20','Male','Lagos');INSERT INTO PersonVALUES (3,'Ameera Abedayo','28','Female','Imo');INSERT INTO PersonVALUES (4,'Maria Elijah','25','Female','Lagos');INSERT INTO PersonVALUES (5,'David Hassan','30','Male','Abuja');INSERT INTO PersonVALUES (6,'Niniola Disu','28','Female','Lagos');INSERT INTO PersonVALUES (7,'Praise Dominion','16','Female','Ibadan');INSERT INTO PersonVALUES (8,'Joe Smith','16','Male','Lagos');-- QuerySELECT name, ageFROM PersonWHERE state IN ('Lagos','Abuja','Ibadan');
Explanation
- Lines 1–7: We create a table called
Personwith the columnsID,name,age,gender, andstate. - Lines 10–25: We insert data into the
Persontable. - Lines 28–30: We filter data based on some conditions using the
WHEREkeyword with theINoperator.
TRENDING TOPICS
