{"id":1246,"date":"2010-01-24T01:29:22","date_gmt":"2010-01-24T09:29:22","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=1246"},"modified":"2023-12-31T03:05:15","modified_gmt":"2023-12-31T10:05:15","slug":"mysql-where","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-where\/","title":{"rendered":"MySQL WHERE"},"content":{"rendered":"\n

Summary: <\/strong>in this tutorial, you will learn how to use the MySQL WHERE<\/code> clause in the SELECT<\/code> statement to filter rows from the result set.<\/p>\n\n\n\n

Introduction to MySQL WHERE clause<\/h2>\n\n\n\n

The WHERE<\/code> clause allows you to specify a search condition for the rows returned by a query. The following shows the syntax of the WHERE<\/code> clause:<\/p>\n\n\n

SELECT<\/span> \n    select_list\nFROM<\/span>\n    table_name\nWHERE<\/span>\n    search_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The search_condition<\/code> is a combination of one or more expressions using the logical operator AND<\/a><\/code>, OR<\/a><\/code> and NOT<\/code>.<\/p>\n\n\n\n
In MySQL, a predicate is a Boolean<\/a> expression that evaluates to TRUE<\/code>, FALSE<\/code>, or UNKNOWN<\/code>.<\/p>\n\n\n\n
The SELECT<\/code> statement will include any row that satisfies the search_condition<\/code> in the result set.<\/p>\n\n\n\n
Besides the SELECT<\/code> statement, you can use the WHERE<\/code> clause in the UPDATE<\/a><\/code> or DELETE<\/code><\/a> statement to specify which rows to update or delete.<\/p>\n\n\n\n
When executing a SELECT<\/code> statement with a WHERE<\/code> clause, MySQL evaluates the WHERE<\/code> clause after the FROM<\/code> clause and before the SELECT<\/code> and ORDER BY<\/code> clauses:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL WHERE clause examples<\/h2>\n\n\n\n
We’ll use the employees<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using the WHERE clause with equality operator example<\/h3>\n\n\n\n
The following query uses the WHERE<\/code> clause to find all employees whose job titles are Sales Rep<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    lastname, \n    firstname, \n    jobtitle\nFROM<\/span>\n    employees\nWHERE<\/span>\n    jobtitle = 'Sales Rep'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+-----------+-----------+\n| lastname  | firstname | jobtitle  |\n+-----------+-----------+-----------+\n| Jennings  | Leslie    | Sales Rep |\n| Thompson  | Leslie    | Sales Rep |\n| Firrelli  | Julie     | Sales Rep |\n| Patterson | Steve     | Sales Rep |\n| Tseng     | Foon Yue  | Sales Rep |\n| Vanauf    | George    | Sales Rep |\n| Bondur    | Loui      | Sales Rep |\n| Hernandez | Gerard    | Sales Rep |\n| Castillo  | Pamela    | Sales Rep |\n| Bott      | Larry     | Sales Rep |\n| Jones     | Barry     | Sales Rep |\n| Fixter    | Andy      | Sales Rep |\n| Marsh     | Peter     | Sales Rep |\n| King      | Tom       | Sales Rep |\n| Nishi     | Mami      | Sales Rep |\n| Kato      | Yoshimi   | Sales Rep |\n| Gerard    | Martin    | Sales Rep |\n+-----------+-----------+-----------+\n17 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the SELECT<\/code> statement examines all rows of the employees<\/code> table and selects only rows whose values are in the jobTitle<\/code> column are Sales Rep<\/code>.<\/p>\n\n\n\n
2) Using the WHERE clause with the AND operator<\/h3>\n\n\n\n
The following example uses the WHERE<\/code> clause to find employees whose job titles are Sales Rep<\/code> and office codes are 1:<\/p>\n\n\n
SELECT<\/span> \n    lastname, \n    firstname, \n    jobtitle,\n    officeCode\nFROM<\/span>\n    employees\nWHERE<\/span>\n    jobtitle = 'Sales Rep'<\/span> AND<\/span> \n    officeCode = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+----------+-----------+-----------+------------+\n| lastname | firstname | jobtitle  | officeCode |\n+----------+-----------+-----------+------------+\n| Jennings | Leslie    | Sales Rep | 1          |\n| Thompson | Leslie    | Sales Rep | 1          |\n+----------+-----------+-----------+------------+\n2 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
jobtitle = 'Sales Rep' AND officeCode = 1;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The AND<\/code> operator evaluates to TRUE<\/code> only if both expressions are evaluated to TRUE<\/code>. Therefore, the query returns rows whose values in the jobTitle<\/code> column is Sales Rep<\/code> and officeCode<\/code> is 1.<\/p>\n\n\n\n
3) Using MySQL WHERE clause with OR operator<\/h3>\n\n\n\n
This query finds employees whose job title is Sales Rep<\/code> or employees who locate the office with office code 1:<\/p>\n\n\n
SELECT<\/span> \n    lastName, \n    firstName, \n    jobTitle, \n    officeCode\nFROM<\/span>\n    employees\nWHERE<\/span>\n    jobtitle = 'Sales Rep'<\/span> OR<\/span> \n    officeCode = 1<\/span>\nORDER<\/span> BY<\/span> \n    officeCode , \n    jobTitle;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+-----------+--------------------+------------+\n| lastName  | firstName | jobTitle           | officeCode |\n+-----------+-----------+--------------------+------------+\n| Murphy    | Diane     | President          | 1          |\n| Bow       | Anthony   | Sales Manager (NA) | 1          |\n| Jennings  | Leslie    | Sales Rep          | 1          |\n| Thompson  | Leslie    | Sales Rep          | 1          |\n| Firrelli  | Jeff      | VP Marketing       | 1          |\n| Patterson | Mary      | VP Sales           | 1          |\n| Firrelli  | Julie     | Sales Rep          | 2          |\n| Patterson | Steve     | Sales Rep          | 2          |\n| Tseng     | Foon Yue  | Sales Rep          | 3          |\n| Vanauf    | George    | Sales Rep          | 3          |\n| Bondur    | Loui      | Sales Rep          | 4          |\n| Hernandez | Gerard    | Sales Rep          | 4          |\n| Castillo  | Pamela    | Sales Rep          | 4          |\n| Gerard    | Martin    | Sales Rep          | 4          |\n| Nishi     | Mami      | Sales Rep          | 5          |\n| Kato      | Yoshimi   | Sales Rep          | 5          |\n| Fixter    | Andy      | Sales Rep          | 6          |\n| Marsh     | Peter     | Sales Rep          | 6          |\n| King      | Tom       | Sales Rep          | 6          |\n| Bott      | Larry     | Sales Rep          | 7          |\n| Jones     | Barry     | Sales Rep          | 7          |\n+-----------+-----------+--------------------+------------+\n21 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
jobtitle = 'Sales Rep' OR officeCode = 1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Therefore, the query returns any employee who has the job title Sales Rep or office code 1.<\/p>\n\n\n\n
4) Using the WHERE clause with the BETWEEN operator example<\/h3>\n\n\n\n
The BETWEEN<\/a><\/code> operator returns TRUE<\/code> if a value is in a range of values:<\/p>\n\n\n
expression BETWEEN low AND high<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query finds employees who are located in offices whose office code is from 1 to 3:<\/p>\n\n\n
SELECT<\/span> \n    firstName, \n    lastName, \n    officeCode\nFROM<\/span>\n    employees\nWHERE<\/span>\n    officeCode BETWEEN<\/span> 1<\/span> AND<\/span> 3<\/span>\nORDER<\/span> BY<\/span> officeCode;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+-----------+------------+\n| firstName | lastName  | officeCode |\n+-----------+-----------+------------+\n| Diane     | Murphy    | 1          |\n| Mary      | Patterson | 1          |\n| Jeff      | Firrelli  | 1          |\n| Anthony   | Bow       | 1          |\n| Leslie    | Jennings  | 1          |\n| Leslie    | Thompson  | 1          |\n| Julie     | Firrelli  | 2          |\n| Steve     | Patterson | 2          |\n| Foon Yue  | Tseng     | 3          |\n| George    | Vanauf    | 3          |\n+-----------+-----------+------------+\n10 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
5) Using the WHERE clause with the LIKE operator example<\/h3>\n\n\n\n
The LIKE<\/a><\/code> operator evaluates to TRUE<\/code> if a value matches a specified pattern. <\/p>\n\n\n\n
To form a pattern, you use the %<\/code> and _<\/code> wildcards. The %<\/code> wildcard matches any string of zero or more characters while the _<\/code> wildcard matches any single character.<\/p>\n\n\n\n
The  following query finds the employees whose last names end with the string 'son'<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    firstName, \n    lastName\nFROM<\/span>\n    employees\nWHERE<\/span>\n    lastName LIKE<\/span> '%son'<\/span>\nORDER<\/span> BY<\/span> firstName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+-----------+\n| firstName | lastName  |\n+-----------+-----------+\n| Leslie    | Thompson  |\n| Mary      | Patterson |\n| Steve     | Patterson |\n| William   | Patterson |\n+-----------+-----------+\n4 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
6) Using the WHERE clause with the IN operator example<\/h3>\n\n\n\n
The IN<\/a><\/code> operator returns TRUE<\/code> if a value matches any value in a list.<\/p>\n\n\n
value IN (value1, value2,...)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the WHERE<\/code> clause with the IN<\/code> operator to find employees who are located in the offices with the codes 1, 2, and 3:<\/p>\n\n\n
SELECT<\/span> \n    firstName, \n    lastName, \n    officeCode\nFROM<\/span>\n    employees\nWHERE<\/span>\n    officeCode IN<\/span> (1<\/span> , 2<\/span>, 3<\/span>)\nORDER<\/span> BY<\/span> \n    officeCode;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+-----------+------------+\n| firstName | lastName  | officeCode |\n+-----------+-----------+------------+\n| Diane     | Murphy    | 1          |\n| Mary      | Patterson | 1          |\n| Jeff      | Firrelli  | 1          |\n| Anthony   | Bow       | 1          |\n| Leslie    | Jennings  | 1          |\n| Leslie    | Thompson  | 1          |\n| Julie     | Firrelli  | 2          |\n| Steve     | Patterson | 2          |\n| Foon Yue  | Tseng     | 3          |\n| George    | Vanauf    | 3          |\n+-----------+-----------+------------+\n10 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
7) Using MySQL WHERE clause with the IS NULL operator<\/h3>\n\n\n\n
To check if a value is NULL<\/a><\/code> or not, you use the IS NULL<\/a><\/code> operator, not the equal operator (=<\/code>). The IS NULL<\/code> operator returns TRUE<\/code> if a value is NULL<\/code>.<\/p>\n\n\n
value IS NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the database world, NULL<\/code> is a marker that indicates that a value is missing or unknown. NULL is not equivalent to the number 0 or an empty string.<\/p>\n\n\n\n
The following statement uses the WHERE<\/code> clause with the IS NULL<\/code> operator to get the rows with the values in the reportsTo<\/code> column are NULL<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    lastName, \n    firstName, \n    reportsTo\nFROM<\/span>\n    employees\nWHERE<\/span>\n    reportsTo IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+----------+-----------+-----------+\n| lastName | firstName | reportsTo |\n+----------+-----------+-----------+\n| Murphy   | Diane     |      NULL |\n+----------+-----------+-----------+\n1 row in set (0.01 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
8) Using MySQL WHERE clause with comparison operators<\/h3>\n\n\n\n
The following table shows the comparison operators that you can use to form the expression in the WHERE<\/code> clause.<\/p>\n\n\n\n
Operator<\/th>Description<\/th><\/tr>
=<\/td>Equal to. You can use it with almost any data type.<\/td><\/tr>
<> or !=<\/td>Not equal to<\/td><\/tr>
<<\/td>Less than. You typically use it with numeric and date\/time data types.<\/td><\/tr>
><\/td>Greater than.<\/td><\/tr>
<=<\/td>Less than or equal to<\/td><\/tr>
>=<\/td>Greater than or equal to<\/td><\/tr><\/thead><\/table><\/figure>\n\n\n\n
The following query uses the not equal to (<>) operator to find all employees who are not the Sales Rep<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    lastname, \n    firstname, \n    jobtitle\nFROM<\/span>\n    employees\nWHERE<\/span>\n    jobtitle <> 'Sales Rep'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Try It Out<\/a><\/p>\n\n\n
+-----------+-----------+----------------------+\n| lastname  | firstname | jobtitle             |\n+-----------+-----------+----------------------+\n| Murphy    | Diane     | President            |\n| Patterson | Mary      | VP Sales             |\n| Firrelli  | Jeff      | VP Marketing         |\n| Patterson | William   | Sales Manager (APAC) |\n| Bondur    | Gerard    | Sale Manager (EMEA)  |\n| Bow       | Anthony   | Sales Manager (NA)   |\n+-----------+-----------+----------------------+\n6 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query finds employees whose office code is greater than 5:<\/p>\n\n\n
SELECT<\/span> \n    lastname, \n    firstname, \n    officeCode\nFROM<\/span>\n    employees\nWHERE<\/span> \n    officecode > 5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+-----------+------------+\n| lastname  | firstname | officeCode |\n+-----------+-----------+------------+\n| Patterson | William   | 6          |\n| Bott      | Larry     | 7          |\n| Jones     | Barry     | 7          |\n| Fixter    | Andy      | 6          |\n| Marsh     | Peter     | 6          |\n| King      | Tom       | 6          |\n+-----------+-----------+------------+\n6 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query returns employees with office code less than or equal to 4 (<=4):<\/p>\n\n\n
SELECT<\/span> \n    lastname, \n    firstname, \n    officeCode\nFROM<\/span>\n    employees\nWHERE<\/span> \n    officecode <= 4<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+-----------+------------+\n| lastname  | firstname | officeCode |\n+-----------+-----------+------------+\n| Murphy    | Diane     | 1          |\n| Patterson | Mary      | 1          |\n| Firrelli  | Jeff      | 1          |\n| Bondur    | Gerard    | 4          |\n| Bow       | Anthony   | 1          |\n| Jennings  | Leslie    | 1          |\n| Thompson  | Leslie    | 1          |\n| Firrelli  | Julie     | 2          |\n| Patterson | Steve     | 2          |\n| Tseng     | Foon Yue  | 3          |\n| Vanauf    | George    | 3          |\n| Bondur    | Loui      | 4          |\n| Hernandez | Gerard    | 4          |\n| Castillo  | Pamela    | 4          |\n| Gerard    | Martin    | 4          |\n+-----------+-----------+------------+\n15 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the WHERE<\/code> clause to filter rows by a condition.<\/li>\n\n\n\n
  • MySQL evaluates the WHERE<\/code> clause after the FROM<\/code> clause and before the SELECT<\/code> and ORDER BY<\/code> clauses.<\/li>\n<\/ul>\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
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
The OR<\/a><\/code> operator evaluates to TRUE<\/code> only if one of the expressions evaluates to TRUE<\/code>:<\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
In this example, the expression in the WHERE<\/code> clause uses the AND<\/a><\/code> operator to combine two conditions:<\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n