How to Select Group of Rows that Match All Items on a List in SQL Server?
Last Updated :
28 Oct, 2021
In this article, we will see, how to select a group of rows that match all the items on a list in SQL Server.
We can perform the above function by two methods. Those are:
- By using IN operator
- BY performing JOIN using STRING_SPLIT method
IN operator: It allows you to specify a list of values in a WHERE clause.
STRING_SPLIT(): this function can be used to split a character expression using a specified separator like comma(‘,’), etc.
For demonstration follow the below steps:
Step 1: Create a database
The database can be created using CREATE command.
Query:
CREATE DATABASE geeks;
Step 2: Using a database
Use the below SQL statement to switch the database context to geeks:
Query:
USE geeks;
Step 3: Table definition
We have the following demo_table in our geek’s database.
Query:
CREATE TABLE demo_table(
NAME VARCHAR(20),
AGE INT,
CITY VARCHAR(20) );
Step 4: Insert data into a table
Query:
INSERT INTO demo_table VALUES
('ROMY KUMARI', 22, 'NEW DELHI'),
('PUSHKAR JHA',23, 'NEW DELHI'),
('RINKLE ARORA',23, 'PUNJAB'),
('AKASH GUPTA', 23, 'UTTAR PRADESH'),
('AKANKSHA GUPTA',22, 'PUNJAB'),
('SUJATA JHA', 30,'PATNA')
('PREETI GIRI', 26,'BANGLORE'),
('PREM GUPTA',31,'PUNE');
Step 5: View data of the table
Query:
SELECT * FROM demo_table;
Output:
Step 6: Select a group of rows matching the items in the list.
For the purpose of demonstration let’s suppose we have a list having the following items: (21, 45, 23, 31). We will select the group of rows whose values in the AGE column match the items in the list.
Method 1: Using IN operator
Syntax:
SELECT *FROM table_name WHERE column_name IN (list);
Query:
SELECT * FROM demo_table WHERE AGE IN (21, 45, 23, 31);
Output:
Method 2: Performing JOIN using STRING_SPLIT() function
Syntax:
SELECT * FROM table_name
JOIN STRING_SPLIT('list', 'separator')
ON value = column_name;
Query:
SELECT * FROM demo_table
JOIN STRING_SPLIT('21, 45, 23, 31', ',')
ON value = AGE;
Output:
Share your thoughts in the comments
Please Login to comment...