How to Select Group of Rows that Match All Items on a List in SQL Server?

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: