Introduction to MySQL WHERE Clause
When working with databases, we use various commands to communicate with the database and perform the required operations. In MySQL, we use SQL that is the structured query language that helps us write the query statements in a certain format understandable to the MySQL server. When writing the statements there is often a necessity to apply certain conditions and restrictions on certain columns of the tables to retrieve the specific expected result set. Where clause in MySQL helps in specifying such conditions that can filter out the result of the table or filter the result set of combinations of multiple tables through the join.
In this article, we will learn about how we can use the where clause in MySQL query statements to filter out the result set, further, we will see its syntax, working and implementation with the help of certain examples.
Syntax:
The syntax of where clause is as defined below:
SELECT list_of_selections FROM name_of_table WHERE filter_conditions;
Some of the terms used in the syntax are explained below:
- List_of_selections: this is the list of column names, expressions, aggregate functions on columns, literals, constants, or nearly any value that you want to retrieve in the result set.
- Name_of_table: This is the name of the table from which you want to retrieve the result set and whose contents you wish to filter out by specifying certain restrictions.
- Filter_conditions: These are the restrictions or conditions that you mention on the columns of the table to get the result set containing records or rows that satisfy those conditions mentioned by you. You can specify one or more conditions with the help of logical operators like AND, OR, and NOT as per your requirement.
Working of MySQL WHERE Clause
This condition is also referred to as the predicate and in MySQL predicate will return to a boolean value that is either true, false, or unknown. only and only when the condition mentioned in where clause evaluates to true the row will be added to the final result set.
We can use the WHERE clause in SELECT, DELETE, and UPDATE statements. The evaluation order of WHERE clause in MySQL query statements is before the SELECT, UPDATE, or DELETE clause whichever is used in the statement and after the FROM clause.
Examples to Implement MySQL WHERE Clause
Let us look at some of the examples of using the WHERE clause to learn its implementation. For that, we will consider a table named developers that we will create in a database named educba and insert some records in it.
Example #1
Let us create the developer’s named table in our database educba that will hold all the above data.
Query:
CREATE TABLE `developers` (
`developer_id` int(11) NOT NULL AUTO_INCREMENT,
`team_id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`position` varchar(100) DEFAULT NULL,
`technology` varchar(100) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`developer_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;
Output:
Example #2
Now, we will insert some records in it using the following query statements.
Query:
INSERT INTO `developers` VALUES
(1,1,'Payal','Developer','Angular',30000),
(2,1,'Heena','Developer','Angular',10000),
(3,3,'Vishnu','Manager','Maven',25000),
(4,3,'Rahul','Support','Digital Marketing',15000),
(5,3,'Siddhesh','Tester','Maven',20000),
(6,7,'Siddharth','Manager','Java',25000),
(7,4,'Brahma','Developer','Digital Marketing',30000),
(8,1,'Arjun','Tester','Angular',19000),
(9,2,'Nitin','Developer','MySQL',20000),
(10,2,'Ramesh','Administrator','MySQL',30000),
(11,2,'Rohan','Admin',NULL,20000),
(12,2,'Raj','Designer',NULL,30000);
4.5 (2,232 ratings)
View Course
Output:
Example #3
Let us first retrieve the records of the table using a simple select query statement.
Query:
SELECT * FROM developers;
Output:
Example #4
Now, suppose that we want to retrieve only those records from developers tables whose salary is greater than 10000 say. For this, we will have to mention a predicate/ condition in the WHERE clause of the SELECT query statement above. Our query statement will be as follows:
Query:
SELECT * FROM developers WHERE salary>10000;
Output:
Example #5
Now, consider a situation where you want to apply multiple conditions on more than one column in a query statement so that when all the specified conditions are fulfilled then only the row should be added into the final resultset of the query. In this case, we can use the AND operator in the WHERE clause. For example, suppose that we want to find out the names of all the developers whose technology is angular and salary is greater than 10000. Then our query statement will be as follows
Query:
SELECT * FROM developers WHERE salary>10000 AND technology = "Angular";
The execution of the above query statement will give the following output including the records whose salary is greater than ten thousand and works with angular technology.
Output:
Example #6
When you have to apply the conditions in such a way that if either of them gets fulfilled then you want that row to be retrieved in the final set then you can use OR operator to specify the conditions in the WHERE clause. Consider one example where we want to retrieve the records of table developers having salary greater than 27000 or is of manager position then our query statement will be as follows –
Query:
SELECT * FROM developers WHERE salary>27000 OR position = "Manager";
The execution of the above query statement will give the following output including the records whose salary is greater than 27000 as well records having a position as a manager that might or not have a salary greater than twenty-five thousand –
Output:
Example #7
When you want to specify the range of the values that will be allowed for a certain column then you can make the use of between keyword to specify so in WHERE clause. Consider that we have to find out the developers whose salary is between 15000 to 22000 then our query statement will be as follows
Query:
SELECT * FROM developers WHERE salary BETWEEN 15000 AND 22000;
Output:
The above query statement gives the following output after execution with all the developers whose salary is in the range of 15000 to 22000.
Along with all the above-mentioned operators, we can make the use of all types of comparative operators that are available such as <,>,<=,>=, etc along with IS NULL and IS NOT NULL and boolean values like TRUE and FALSE in WHERE clause. It functions in the same manner as that of with SELECT statement
Conclusion
We can make the use of WHERE clause in MySQL query statements to specify the conditions and predicates related to the columns of the table and if that predicate evaluates to true then and then only that row is added to the final result set. WHERE clause can be used with SELECT, DELETE, and UPDATE statements. We can use different logical operators, comparative operators, and boolean values in the WHERE clause. IS NULL and IS NOT NULL constraints and also be specified for certain columns inside the WHERE clause.
Recommended Articles
This is a guide to MySQL WHERE Clause. Here we discuss the Introduction of MySQL WHERE Clause and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
