This article will show you how to use the MySQL WHERE clause to filter rows for a specific condition. Using the where clause, we can specify a search condition for rows that return true for the condition and perform actions on them.
Basic Usage
The syntax for the WHERE clause is:
SELECT column_list FROM TABLE_NAME WHERE condition;
We start by defining the initial action to perform; this can be a SELECT, DELETE, UPDATE, or any other supported statement.
Next, we specify the columns and the table on which to perform the specified action.
Finally, we call the WHERE clause followed by the condition to evaluate; this can be a single condition that returns a Boolean value. We can also combine multiple conditions using logical operators such as AND, OR, or NOT.
You can think of the WHERE clause in MySQL as an if statement in any programming language. MySQL compares if the rows meet the specified condition, and if true, it returns them to the calling statement.
The common operators you can use with the WHERE clause include:
| OPERATOR | FUNCTIONALITY |
|---|---|
| = EQUALITY | The equality operator checks if the two sets of values are equal to each other. |
| >= greater than or equal to | Evaluates if the left value is greater than or equal to the value on the right. |
| <= less than or equal to | Evaluates if the value on the left is less than or equal to |
| > greater than | Checks if the value on the left is greater than the value on the right |
| < less than | Evaluates if the value on the left is less than the value on the right |
| != or > not equal | Evaluates if both values are not equal. |
If the condition specified in the WHERE clause does not match any row in the table, it does not return any row.
MySQL Where Clause Examples
Let us illustrate how to use the WHERE clause using various conditions.
Example 1
We can use the WHERE clause with a single condition. Consider the film table in the sakila database.
SELECT title, release_year, LENGTH, rating FROM film LIMIT 5;
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/1-27.jpg" data-lazy- height="197" src="data:image/svg xml,” width=”496″>
We can use the WHERE clause to get the films with a length of precisely 120 mins. To do this, we can use the query:
SELECT title, release_year, LENGTH, rating FROM film WHERE LENGTH = 120;
In the example above, we use the equal (=) operator to get the films with a length of 120. An example output is below:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/2-27.jpg" data-lazy- height="270" src="data:image/svg xml,” width=”689″>
Example 2
We can also combine multiple conditions using the AND logical operator. In this case, both conditions need to evaluate to true.
For example, in the film table above, we can get the films with a length of 120 mins and a replacement_cost of greater than 10.
SELECT title, release_year, LENGTH, rating, replacement_cost FROM film WHERE LENGTH = 120 AND replacement_cost > 10;
In this example, the film needs to have a length of 120 mins and a replacement cost greater than 10.
The query above should return values as:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/3-26.jpg" data-lazy- height="272" src="data:image/svg xml,” width=”739″>
Example 3
Another logical operator we can use with the WHERE clause is the OR operator. In this case, only one condition needs to be true.
For example, we can fetch films with a length greater than 150 or a replacement_cost greater than or equal to 18.
The following query illustrates how to use such a condition.
SELECT title, release_year, LENGTH, rating, replacement_cost FROM film WHERE LENGTH > 150 OR replacement_cost >= 18 LIMIT 10;
In this case, we get many values as we only need the film to have a length greater than 150 or a replacement_cost equal to or greater than 18.
An example printout is below:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/4-24.jpg" data-lazy- height="294" src="data:image/svg xml,” width=”690″>
Example 4
Yet another implementation of the WHERE clause is by using the BETWEEN parameter. In such a scenario, we can specify a range of values to check.
For example, in the film table (see sakila database), we can fetch films with a length between 120 and 150.
The following is an example query:
SELECT title, release_year, LENGTH, rating, replacement_cost FROM film WHERE LENGTH BETWEEN 120 AND 150 LIMIT 10;
An example printout is below:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/5-22.jpg" data-lazy- height="285" src="data:image/svg xml,” width=”783″>
Example 5
We can also implement the WHERE condition using the LIKE statement. In this case, we find a matching case using MySQL LIKE wildcards. Check out the MySQL LIKE tutorial to learn more.
For example, let us take the actor table in the same sakila database. We can find the actors where the first_name includes an m.
SELECT * FROM actor WHERE first_name LIKE ‘%m’;
The above query returns the results as:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/7-20.jpg" data-lazy- height="248" src="data:image/svg xml,” width=”622″>
Conclusion
This article has walked you through how to use the MySQL clause to check for a specific condition and return the result.
Thank you for reading!
About the author
<img alt="" data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/john-150×150.png61265900292e2.jpg" height="112" src="data:image/svg xml,” width=”112″>
John Otieno
My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list
