The syntax for the WHERE clause is:
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:
|= 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.
We can use the WHERE clause with a single condition. Consider the film table in the sakila database.
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:
In the example above, we use the equal (=) operator to get the films with a length of 120. An example output is below:
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.
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:
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.
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:
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:
An example printout is below:
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.
The above query returns the results as:
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!