Summary: in this tutorial, you will learn how to use the MariaDB where clause to filter rows returned by a query.
Introduction to MariaDB where clause
The where clause is an optional clause of the select statement, which specifies a search condition for selecting rows.
Here is the syntax of a select statement with a where clause:
select select_list from table_name where search_condition order by sort_expression; Code language: SQL (Structured Query Language) (sql)In this syntax, you specify a search condition to select rows from a table.
If a row causes the search_condition to evaluate to true, the where clause includes the row in the result set.
The search condition may consist of one or multiple logical expressions. A logic expression always evaluates to one of three values true, false, or unknown. The logical expressions in the where clause is also known as predicates.
In the select statement, the where clause appears after the from clause and before the order by clause.
MariaDB evaluates the clauses in the select statement that consists of select, from, where, and order by clauses in the following sequence: from, where, select, order by:
In addition to the select statement, you can use the where clause in the update or delete statement to specify rows to update or delete.
MariaDB where clause examples
All the following examples will use the countries table from the sample database:
A) Using the MariaDB where clause with the equal (=) operator
The following example uses the where clause to select countries that have the region id 2:
select name, area, region_id from countries where region_id = 2 order by name; Code language: SQL (Structured Query Language) (sql)
B) Using the MariaDB where clause with comparison operators
The following statement uses the where clause to find countries whose areas are greater than 2,000,000 km2:
select name, area from countries where area > 2000000 order by area; Code language: SQL (Structured Query Language) (sql)
C) Using the MariaDB where clause with the and operator
The and operator combines boolean expressions and only returns true if both expressions return true.
The following example uses the where clause with the and operator to find countries that are in region id 1 and have areas greater than 2,000,000 km2:
select name, area, region_id from countries where region_id = 2 and area > 2000000 order by name; Code language: SQL (Structured Query Language) (sql)
C) Using the MariaDB where clause with the or operator
Like the and operator, the or operator is used to combine logical expressions. However, the or operator returns true if at least one of the expressions returns true.
The following statement uses the or operator in the where clause to find countries that locate in region 2 or have areas greater than 2,000,000 km2:
select name, area, region_id from countries where region_id = 2 or area > 2000000 order by name; Code language: SQL (Structured Query Language) (sql)
D) Using the MariaDB where clause to find rows whose values are between two values
The between operator returns true if a value is between two values:
expression between low and high Code language: SQL (Structured Query Language) (sql)It is a shortcut of:
expression >= low and expression <= high Code language: SQL (Structured Query Language) (sql)For example, this statement uses the between operator in the where clause to find countries that have areas between 1,001,449 and 1,566,500 km2:
select name, area from countries where area between 1001449 and 1566500 order by area; Code language: SQL (Structured Query Language) (sql)
E) Using the MariaDB where clause to find rows whose values are in a list
To check if a value is in a list of value, you use the in operator:
expression in (value1, value2,...) Code language: SQL (Structured Query Language) (sql)The in operator returns true if the expression evaluates to one of the value list value1, value2, …
The following statement uses the in operator in the where clause to find countries whose country codes are in the US, FR and JP country codes:
select name, country_code2 from countries where country_code2 in ('US','FR','JP') order by name; Code language: SQL (Structured Query Language) (sql)
F) Using the MariaDB where clause to find rows based on a pattern
The like operator returns true if a value matches a specific pattern
expression LIKE pattern; Code language: SQL (Structured Query Language) (sql)To construct a pattern, you use two wildcards:
%matches one or more characters_matches one character
The following example uses the like operator in the where clause to find countries whose names start with the letter J:
select name from countries where name like 'J%' order by name; Code language: SQL (Structured Query Language) (sql)
In this tutorial, you will have learned how to use the MariaDB where clause to specify a search condition for selecting rows.
