Gice

Technology and General Blog 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;

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: 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: 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: 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: 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: Conclusion

This article has walked you through how to use the MySQL clause to check for a specific condition and return the result. 