SQL Query for Matching Multiple Values in the Same Column

Querying multiple values within a single column is a vital skill in SQL, enabling users to filter and retrieve data based on specific criteria. Whether we're working with large datasets or simple tables, mastering techniques like the IN clause, LIKE operator, and comparison operators (e.g., >=) ensures efficient and precise data handling.

These methods are especially useful when dealing with scenarios that require matching patterns, comparing numerical values, or checking for multiple conditions simultaneously. This guide focuses on using these techniques in Microsoft SQL Server, with detailed examples to make the concepts accessible for both beginners and experienced professionals.

Step-by-Step Guide: Matching Multiple Values in a Single Column

Efficiently matching multiple values in a single column is essential for filtering and retrieving data in SQL. This guide walks us through practical techniques using operators like IN, LIKE, and comparison operators (>=) to streamline our queries and enhance data handling.

Step 1: Create a Table

Create a table CARS inside the database GeeksForGeeks. This table has three columns: CAR_NAME, COMPANY, and COST containing the name, company, and cost of various cars. This command defines the structure of the CARS table.

Query:

CREATE TABLE CARS(
CAR_NAME VARCHAR(10),
COMPANY VARCHAR(10),
COST INT);

Step 5: Insert Data into the Table

Populate the CARS table with sample data for demonstration purposes. Each INSERT statement adds a row to the CARS table. The data includes car names, their respective companies, and their costs. This sample data will be used for querying in subsequent steps.

Query:

INSERT INTO CARS VALUES('INNOVA','TOYOTA',10000);
INSERT INTO CARS VALUES('CAMRY','TOYOTA',20000);
INSERT INTO CARS VALUES('CIAZ','HONDA',30000);
INSERT INTO CARS VALUES('POLO','VOLKSWAGEN',50000);
INSERT INTO CARS VALUES('BENZ','MERCEDES',100000);

Step 6: Display All Rows

Retrieve all rows from the CARS table to verify the inserted data. The SELECT * statement retrieves all columns and rows from the CARS table, displaying the current data stored in the table.

Query:

SELECT * FROM CARS;

Output

Step 7: Match Multiple Values Using IN

Retrieve the details of all the cars belonging to the companies TOYOTA and HONDA. The IN operator checks if the value in the COMPANY column matches any of the specified values (TOYOTA or HONDA). So, this query retrieves all cars manufactured by Toyota and Honda.

Syntax

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);

Query:

SELECT * FROM CARS WHERE COMPANY IN ('TOYOTA','HONDA');

Output

Step 8: Match Values Using LIKE

Retrieve the details of all the cars whose name starts with the letter C. The LIKE operator is used for pattern matching. The % symbol represents zero or more characters. This query retrieves all rows where the CAR_NAME starts with the letter 'C', such as CAMRY and CIAZ.

Syntax

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME LIKE 'STARTING_LETTER%';

Query:

SELECT * FROM CARS WHERE CAR_NAME LIKE 'C%';

Output

Step 9: Filter Data Using >= Operator

Retrieve the details of all the cars having cost greater than or equal to 30000. The >= operator compares the COST column against the specified value. This query retrieves all rows where the cost is 30,000 or higher.

Syntax

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME >=VALUE;

Query:

SELECT * FROM CARS WHERE COST>=30000;

Output

Conclusion

In SQL, querying multiple values in the same column is a common task that can be efficiently handled using the IN, OR and EXISTS operators. These operators allow to filter rows based on the multiple matching criteria within a single column. When dealing with the more complex cases such as the matching multiple conditions for the multiple rows, GROUP BY, HAVING and JOIN operations come into the play. The Optimizing queries for the performance by choosing the right operator and index structure is crucial for the large datasets.