WHERE Clause in SQL with Multiple values

I was using the WHERE clause in SQL to filter records and perform operations such as CREATE, READ(SELECT), UPDATE, or DELETE. But I don’t know how to set the condition for checking multiple values ​​in a column, then I found the solution for that, we need to use the ‘IN’ operator to check the condition by passing multiple values ​​in the clause. We can significantly improve query flexibility and performance by leveraging its capability WHERE clause with multiple values. This article covers how to conditionally set multiple values ​​for a column in the SQL WHERE clause

WHERE Clause

Before we check multiple values, let’s see how ‘WHERE’ works. We can use the WHERE clause to filter specific data in tables by giving a condition. Various conditions can be defined to filter the data using comparison operators or logical operators. For example, if you want to filter the name “john” in the column “name”, you can set the condition “name = ‘john'”.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

We have to set the condition after putting the “WHERE” keyword.

Example:

In the below example, we create a table employee with columns (empID, empName, job_Dec, salary) and inserted some data. In this table, we set the condition (name = 'kevin‘) using the WHERE clause, so it will display only the row containing the ‘Kevin’ name.

--Creating New Table 
CREATE TABLE employee(
empID INT PRIMARY KEY,
empName VARCHAR(30),
job_Dec VARCHAR(20),
salary INT
);

--Inserting the data
INSERT INTO employee VALUES(1, 'John', 'ADMIN', 10000);
INSERT INTO employee VALUES(2, 'Smith', 'HR', 30000);
INSERT INTO employee VALUES(3, 'Rocky', 'SALES', 20000);
INSERT INTO employee VALUES(4, 'kevin', 'MANAGER', 320000);
INSERT INTO employee VALUES(5, 'julie', 'SALES', 25000);

--Display the data with Filtering
SELECT * FROM employee
WHERE empName='kevin'

Here, we have given (*) star which means all columns are displayed, if you give a specific column name instead of the star, only that column will be displayed, like:

SELECT empName, salary FROM employee
WHERE empName='kevin'

Multiple Values in a column

To check multiple values ​​in a column, you need to use the ‘IN’ operator. If you check the condition by giving multiple values ​​to a columnName using IN operator, it will display the corresponding rows to the values ​​you have given. When we need to use multiple “OR” operators, we can use the IN operator because IN operator is a shorthand method for Multiple “OR”.

In the following way, we can also set the condition by giving multiple values ​​using the “OR” operator.

SELECT * FROM employee
WHERE salary = 25000 OR salary = 30000 OR salary = 10000

But this way is not a proper way to check the multiple values because it increases the number of lines of code.  So, you can check the multiple values ​​in a column by following  the IN operator syntax:

WHERE column_name IN (value1, value2, value3, ...);

The example below does the same work as the OR operator, but in this method, we can check multiple values by just putting a comma (,).

SELECT * FROM employee
WHERE salary IN (25000, 30000, 10000 )
image 3

Multiple values in multiple columns

To check multiple values ​​in a column, we need to use the both ‘IN’ and ‘OR‘ operators. Using these two operators we can check multiple values ​​for two or more columns by the following syntax:

WHERE columnName1 IN (value1, value2, value3, ...) OR columnName2 IN (value1, value2, value3, ...) ...

Example:

SELECT * FROM employee
WHERE salary IN (25000, 30000) OR job_Dec IN ('MANAGER','HR')

In this example, we set the condition whether salary column has 25000, 30000 values ​​or job_Dec column has 'MANAGER','HR‘ values. Both conditions are true in theemployee table, the corresponding rows of the two conditions are displayed. Since both salary :’30000‘ and job_Dec: “HR” is in the same row, so 2nd empID is displayed first.

Conclusion

So, you can set multiple values ​​to a column as a condition of the WHERE clause using the “IN” operator.