916123, 197 AM MySaL WHERE IW mysqutuTorIAL MySQL WHERE Summary: in this tutorial, you will learn how to use the MySQL where clause in the seLecr statement to filter rows from the result set. Introduction to MySQL WHERE clause The WHERE clause allows you to specify a search condition for the rows returned by a query. The following shows the syntax of the WHERE clause: SELECT FROM WHERE The search_condition is a combination of one or more expressions using the logical operator AND (https: //tam.nysqleutorial.org/nysal-and/) , OR (hetps://mw.aysqltutorial.org/mysal-or/) and NOT In MySQL, a predicate is a Boolean expression that evaluates to TRUE, FALSE , OF UNKNOWN The select statement will include any row that satisfies the search_condition in the result set, Besides the se.ect statement, you can use the WHERE clause in the UPDATE (https: //wm.nysqitutorial org/nysql-update-data.aspx) OF DELETE (httpsi//www.mysqltutorial org/mysql-delete- statementaspx) Statement to specify which rows to update or delete. When executing a SELECT statement with a wHere clause, MySQL evaluates the WHERE clause after the From clause and before the select and orper By clauses: FROM >! were >! seteot |_—>lorner ay tps uw. mysaltstorialorgimysahwheret916123, 197 AM MySaL WHERE MySQL WHERE clause examples We'll use the employees table from the sample database (https://www.mysaltutorial.org/mysql-sample- database.aspx) for the demonstration. employees * employeeNumber lastNeme firstName extension email officeCode Lo reportsTo jobTitle 1) Using MySQL WHERE clause with equality operator example The following query uses the wHere clause to find all employees whose job titles are sales Rep SELECT FROM WHERE Sales Rep’ | lastname | firstname | jobtitle | | Jennings | Leslie | Sales Rep | | Thompson | Leslie | Sales Rep | | Firrelli | Julie I sales Rep | | Patterson | steve | Sales Rep | | Tseng | Foon Yue | Sales Rep | | Vanauf | George | Sales Rep | tps uw. mysaltstorialorgimysahwheret916123, 197 AM MySaL WHERE | Bondur | Loui I sales Rep | | Hernandez | Gerard | Sales Rep | | Castillo | Pamela | Sales Rep | | Bott | Larry | Sales Rep | | Jones | Barry _| Sales Rep | | Fixter | Andy I sales Rep | | Marsh | Peter I Sales Rep | | King | Tom | sales Rep | | Nishi | Mami I sales Rep | | Kato | Yoshimi | Sales Rep | | Gerard | Martin | Sales Rep | 17 rows in set (@.00 sec) nthis example, the SELECT statement examines all rows of the employees. table and selects only rows whose values in the jobTitle column are Sales Rep 2) Using MySQL WHERE clause with the AND operator The following example uses the were clause to find employees whose job titles are sales rep and office codes are 1: SELECT FROM WHERE Sales Rep’ AND 1 > | lastname | firstname | jobtitle | officeCode | * | Jennings | Leslie | Sales Rep | 1 \ | Thompson | Leslie | Sales Rep | 1 | tps uw. mysaltstorialorgimysahwheret ana916123, 197 AM MySaL WHERE 2 rows in set (0.08 sec) In this example, the expression in the HERE clause uses the AND (hctps://imw.nysaltutorsal.org/nysal- and/) operator to combine two conditions: The AND operator evaluates to TRUE only if both expressions evaluate to. TRUE . Therefore, the query returns rows whose values in the jobTitle columnis Sales Rep and officecode is 1. 3) Using MySQL WHERE clause with OR operator This query finds employees whose job title is sales Rep or employees who locate the office with office code 1: SELECT FROM WHERE Sales Rep’ OR 1 ORDER BY > | lastName | FirstName | jobTitle | oF Ficecode | | Murphy | Diane | President 1a | | Bow | anthony | Sales Manager (NA) | 2 | | Jennings | Leslie | Sales Rep i tps uw. mysaltstorialorgimysahwheret916123, 197 AM MySaL WHERE | Thompson | Leslie | Sales Rep [a | | Firrelli | Jeff | vP Marketing [a | | Patterson | Mary | vp sales 1a | | Firrelli | Julie | Sales Rep 12 | | Patterson | Steve | Sales Rep 12 | | Tseng | Foon Yue | Sales Rep 13 | | Vanauf | George | Salles Rep 13 | | Bondur | Loui | sales Rep 14 | | Hernandez | Gerard | Sales Rep 14 | | Castillo | Pamela | Sales Rep la | | Gerard | Martin | Salles Rep 14 | | Nishi | Mami | sales Rep Is | | Kato | Yoshimi | Sales Rep Is | | Fixter | Andy | sales Rep le | | Marsh | Peter | sales Rep 16 | | King | Tom | sales Rep le | | Bott | Larry | Sales Rep 17 | | Jones | Barry —| Salles Rep 17 | 21 rows in set (@.00 sec) The OR (netos://ww.ryseltutorial.org/sysel-or/) Operator evaluates to TRUE only if one of the expressions evaluates to. TRUE : Therefore, the query returns any employee who has the job title Sales Rep or office code 1 4) Using MySQL WHERE clause with the BETWEEN operator example The BETWEEN (netps://som.nyscltutorsal.org/aysql-between) Operator returns TRUE if a value is in a range of values: The following query finds employees who locate in offices whose office code is from 1 to 3: SELECT tps uw. mysaltstorialorgimysahwheret916123, 197 AM MySaL WHERE FROM WHERE BETWEEN 1 AND 3 ORDER BY > | FirstName | lastName | officecode | | Diane | Murphy = J 2 | | Mary | Patterson | 1 | | aeFF | Firrelli | 1 | | anthony | Bow Ia | | Leslie | Jennings | 1 | | Leslie | Thompson | 2 | | aulie | Firrelli | 2 | | Steve | Patterson | 2 | | Foon Yue | Tseng 1 3 | | George | Vanauf = | 3 | + + 1@ rows in set (8.00 sec) 5) Using MySQL WHERE clause with the LIKE operator example The LIKE (hetps://omu.nysqltutorial.org/nysql-1ike/) Operator evaluates to TRUE if a value matches a specified pattern. To form a pattern, you use the % and _ wildcards. The % wildcard matches any string of zero or more characters while the __ wildcard matches any single character. The following query finds the employees whose last names end with the string “son SELECT FROM tps uw. mysaltstorialorgimysahwheret wa916123, 197 AM MySaL WHERE WHERE LIKE '% ORDER BY + | firstName | lastNane | | Leslie | Thompson | | Mary | Patterson | | steve | Patterson | | William | Patterson | + 4 rows in set (0.00 sec) 6) Using MySQL WHERE clause with the IN operator example The IN (netps://uw.rysaituterial org/sysqi-basics/aysal-iry) Operator returns TRUE if a value matches any value in alist The following example uses the wHere clause with the 1Nn operator to find employees who locate in the office with office code 1. SELECT FROM WHERE IN (1, 2, 3) ORDER BY tps uw. mysaltstorialorgimysahwheret m2916123, 197 AM MySaL WHERE > | FirstName | lastName | officeCode | | Diane | Murphy =} a | | Mary | Patterson | 1 | | aeFF | Firrelli | 1 | | Anthony | Bow 1a | | Leslie | Jennings | 2 | | Leslie | Thompson | 1 | | Julie | Firrelli | 2 | | Steve | Patterson | 2 | | Foon Yue | Tseng = | 3 | | George | Vanauf =| 3 | 1@ rows in set (@.00 sec) 7) Using MySQL WHERE clause with the IS NULL operator To check if a value is NULL nesps://mu.nysqltutorsat.org/aysql-null/) OF Not, you use the IS NULL (ottns://mm.aysqlsutorsal.org/nys@l-is-null/) operator, not the equal operator ( = ). The TS NULL operator returns TRUE ifa value is NULL In the database world, nuit is a marker that indicates that a value is missing or unknown. And NULL is not equivalent to the number 0 or an empty string The following statement uses the wHeRe clause with the 15 NULL operator to get the rows with the values inthe reportsTo column are NULL : SELECT FROM tps uw. mysaltstorialorgimysahwheret ana916123, 197 AM WHERE IS NULL. | lastName | FirstName | reportsto | te | Murphy | Diane | NULL | 1 row in set (0.01 sec) MySaL WHERE 8) Using MySQL WHERE clause with comparison operators The following table shows the comparison operators that you can use to form the expression in the WHERE clause. Operator —_Description = Equal to. You can use it with almost any data type. <> orl= Not equal to < Less than. You typically use it with numeric and date/time data types. > Greater than. <= Less than or equal to Ds Greater than or equal to The following query uses the not equal to (<>) operator to find all employees who are not the sales Rep SELECT tps uw. mysaltstorialorgimysahwheret916123, 197 AM MySaL WHERE FROM WHERE sales Rep > | lastname | firstname | jobtitle | + | Murphy | Diane | President | | Patterson | Mary | vp sales | | Firrelli | Jeff | vP Marketing | | Patterson | William | Sales Manager (APAC) | | Bondur | Gerard | Salle Manager (EMEA) | | Bow | anthony | Sales Manager (NA) | 6 rows in set (0.0@ sec) The following query finds employees whose office code is greater than 5 SELECT FROM WHERE | lastname | firstname | officeCode | | Patterson | Willian | 6 | | Bott Jarry 17 | | Jones | Barry = 7 | | Fixter — | Andy l6 | | Marsh | Peter 16 | tps uw. mysaltstorialorgimysahwheret9183, 197 AM | King | Tom Ie + + 6 rows in set (0.8 sec) The following query returns employees SELECT FROM WHERE > + | lastname | firstname | officecode | | Murphy | Diane = J a I | Patterson | Mary Ia I | Firrelli | eft Ia | | Bondur | Gerard | 4 | | Bow | Anthony | 2 I | Jennings | Leslie | 4 I | Thompson | Leslie | 2 I | Firrelli | Julie | 2 | | Patterson | Steve 12 | | Tseng | Foon Yue | 3 | | vanauf | George | 3 I | Bondur | Lou la I | Hernandez | Gerard | 4 I | castillo | Pamela | 4 | | Gerard | Martin =| 4 | + + + ) 15 rows in set (0.00 sec Summary MySaL WHERE office code less than or equal to 4 (: # Use the were clause to filter rows by a condition tps uw. mysaltstorialorgimysahwheret ne916123, 197 AM MySaL WHERE ¢ MySQL evaluates the WHERE clause after the From clause and before the seLect and oRDER BY clauses. tps uw. mysaltstorialorgimysahwheret