How to Select Multiple Values Using WHERE in MySQL
-
the
WHERE
Clause in SQL Queries -
Select Records Based on Multiple Conditions Using the
WHERE
Clause in MySQL
This article is about using MySQL queries to get the data from specific tables or relations that can meet certain criteria. For this, a WHERE
clause is used in SQL queries.
the WHERE
Clause in SQL Queries
The WHERE
clause specifies criteria for retrieving data from a single table or joining multiple tables. The query returns the corresponding value from the table if the provided condition is met.
You can use the WHERE
clause to restrict the records and only get those required.
The WHERE
clause is used not just in the SELECT
statement but also in the UPDATE
, DELETE
, and other statements.
Syntax of the WHERE
Clause
In SQL, the following syntax is used for the WHERE
clause to filter the record based on specific conditions.
SELECT column1, column2, ...
FROM table_name
WHERE [condition];
The condition can be made by using different relational (<
, >
, <=
, >=
, ==
, !=
) or logical (AND
, OR
, NOT
) operators.
Assume that we have an Employees
table that saves employees’ data of an organization. The table data is shown below:
The above table shows the data of 6 employees from the Employees
table. Suppose we need to select the employees whose age is greater than 40, then we will use the following query:
SELECT * from Employees
WHERE Emp_Age > 40
Output:
Note that we have used a relational operator in the WHERE
clause. Similarly, we can use logical and relational operators in the WHERE
clause.
Select Records Based on Multiple Conditions Using the WHERE
Clause in MySQL
We can also filter the records from the table based on multiple conditions. For this, we can use logical operators like AND
and OR
based on our conditions.
For instance, we need to get the names and salaries of employees under 40 years of age and salaries greater than $3000. The query for this condition will be:
SELECT Emp_Name, Emp_Salary FROM `Employees`
WHERE Emp_Age < 40 AND Emp_Salary > 3000
The result of this query will be:
You can see from the result that only those employees who meet both of the conditions specified in the query are selected. If we need any one of the conditions to be fulfilled, then we can make the use of the OR
operator instead of the AND
operator, like this:
SELECT Emp_Name, Emp_Salary FROM `Employees`
WHERE Emp_Age < 40 OR Emp_Salary > 3000
Now the result set would be like this:
You can see the result contains more rows than the previous result. This is because all employees have either age less than 40 or their salary is more significant than $3000.
The same results can also be obtained using the IN
operator. The IN
operator works the same as the OR
operator, except that the query’s structure is better.
SELECT Emp_ID,Emp_Name, Emp_Age FROM `Employees`
WHERE Emp_Name IN ("John","David")
The results set would be like this:
In SQL queries, you can see multiple ways to select multiple values from the tables. Any of them can be opted based on your needs and desired data output.
Husnain is a professional Software Engineer and a researcher who loves to learn, build, write, and teach. Having worked various jobs in the IT industry, he especially enjoys finding ways to express complex ideas in simple ways through his content. In his free time, Husnain unwinds by thinking about tech fiction to solve problems around him.
LinkedIn