MariaDB Where

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.