{"id":167,"date":"2019-10-05T10:47:05","date_gmt":"2019-10-05T03:47:05","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=167"},"modified":"2020-04-11T23:24:10","modified_gmt":"2020-04-11T16:24:10","slug":"mariadb-where","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-where\/","title":{"rendered":"MariaDB Where"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB where<\/code> clause to filter rows returned by a query.<\/p>\n\n\n\n

Introduction to MariaDB where clause<\/h2>\n\n\n\n

The where<\/code> clause is an optional clause of the select<\/a><\/code> statement, which specifies a search condition for selecting rows.<\/p>\n\n\n\n

Here is the syntax of a select<\/code> statement with a where<\/code> clause:<\/p>\n\n\n

select<\/span>\n    select_list\nfrom<\/span>\n    table_name\nwhere<\/span>\n    search_condition\norder<\/span> by<\/span>\n    sort_expression;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify a search condition to select rows from a table.<\/p>\n\n\n\n
If a row causes the search_condition<\/code> to evaluate to true<\/code>, the where<\/code> clause includes the row in the result set.<\/p>\n\n\n\n
The search condition may consist of one or multiple logical expressions. A logic expression always evaluates to one of three values true<\/code>, false<\/code>, or unknown<\/code>. The logical expressions in the where<\/code> clause is also known as predicates.<\/p>\n\n\n\n
In the select<\/code> statement, the where<\/code> clause appears after the from<\/code> clause and before the order by<\/code> clause.<\/p>\n\n\n\n
\"mariadb<\/figure>\n\n\n\n
In addition to the select<\/code> statement, you can use the where<\/code> clause in the update<\/code> or delete<\/code> statement to specify rows to update or delete.<\/p>\n\n\n\n
MariaDB where clause examples<\/h2>\n\n\n\n
All the following examples will use the countries<\/code> table from the sample database:<\/p>\n\n\n\n
A) Using the MariaDB where clause with the equal (=) operator<\/h3>\n\n\n\n
The following example uses the where<\/code> clause to select countries that have the region id 2:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    area, \n    region_id\nfrom<\/span> \n    countries\nwhere<\/span> \n    region_id = 2<\/span>\norder<\/span> by<\/span>\n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/figure>\n\n\n\n
B) Using the MariaDB where clause with comparison operators<\/h3>\n\n\n\n
The following statement uses the where<\/code> clause to find countries whose areas are greater than 2,000,000<\/code> km2:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    area\nfrom<\/span> \n    countries\nwhere<\/span> \n    area > 2000000<\/span>\norder<\/span> by<\/span> \n    area;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/figure>\n\n\n\n
C) Using the MariaDB where clause with the and operator<\/h3>\n\n\n\n
The and<\/code> operator combines boolean expressions and only returns true if both expressions return true.<\/p>\n\n\n\n
The following example uses the where<\/code> clause with the and<\/code> operator to find countries that are in region id 1 and have areas greater than 2,000,000<\/code> km2:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    area, \n    region_id\nfrom<\/span> \n    countries\nwhere<\/span> \n    region_id = 2<\/span> and<\/span> \n    area > 2000000<\/span>\norder<\/span> by<\/span> \n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/h3>\n\n\n\n
C) Using the MariaDB where clause with the or operator<\/h3>\n\n\n\n
Like the and operator, the or operator is used to combine logical expressions. However, the or<\/code> operator returns true if at least one of the expressions returns true.<\/p>\n\n\n\n
The following statement uses the or<\/code> operator in the where clause to find countries that locate in region 2 or have  areas greater than 2,000,000<\/code> km2:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    area, \n    region_id\nfrom<\/span> \n    countries\nwhere<\/span> \n    region_id = 2<\/span> or<\/span> \n    area > 2000000<\/span>\norder<\/span> by<\/span> \n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/figure>\n\n\n\n
D) Using the MariaDB where clause to find rows whose values are between two values<\/h3>\n\n\n\n
The between<\/code> operator returns true if a value is between two values:<\/p>\n\n\n
expression between low and high\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It is a shortcut of:<\/p>\n\n\n
expression >= low and expression <= high\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, this statement uses the between<\/code> operator in the where clause to find countries that have areas between 1,001,449<\/code> and 1,566,500<\/code> km2:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    area\nfrom<\/span> \n    countries\nwhere<\/span> \n    area between<\/span> 1001449<\/span> \n        and<\/span> 1566500<\/span>\norder<\/span> by<\/span> \n    area;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/figure>\n\n\n\n
E) Using the MariaDB where clause to find rows whose values are in a list<\/h3>\n\n\n\n
To check if a value is in a list of value, you use the in<\/code> operator:<\/p>\n\n\n
expression in (value1, value2,...)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The in operator returns true if the expression evaluates to one of the value list value1, value2, …<\/p>\n\n\n\n
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:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    country_code2\nfrom<\/span> \n    countries\nwhere<\/span> \n    country_code2 in<\/span> ('US'<\/span>,'FR'<\/span>,'JP'<\/span>)\norder<\/span> by<\/span> \n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/figure>\n\n\n\n
F) Using the MariaDB where clause to find rows based on a pattern<\/h3>\n\n\n\n
The like<\/code> operator returns true if a value matches a specific pattern<\/p>\n\n\n
expression LIKE pattern;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To construct a pattern, you use two wildcards:<\/p>\n\n\n\n
  • %<\/code> matches one or more characters<\/li>
  • _<\/code> matches one character<\/li><\/ul>\n\n\n\n
    The following example uses the like operator in the where clause to find countries whose names start with the letter J<\/code>:<\/p>\n\n\n
    select<\/span> \n    name<\/span>\nfrom<\/span> \n    countries\nwhere<\/span> \n    name<\/span> like<\/span> 'J%'<\/span>\norder<\/span> by<\/span> \n    name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"mariadb<\/figure>\n\n\n\n
    In this tutorial, you will have learned how to use the MariaDB where<\/code> clause to specify a search condition for selecting rows.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
MariaDB evaluates the clauses in the select statement that consists of select<\/code>, from<\/code>, where<\/code>, and order by<\/a><\/code> clauses in the following sequence: from<\/code>, where<\/code>, select<\/code>, order by<\/code>:<\/p>\n\n\n\n