MySQL WHERE

 MySQL WHERE

Summary: in this tutorial, you will learn how to use the MySQL WHERE clause in the SELECT statement to filter rows from the result set.

Introduction to MySQL WHERE clause

The WHERE the 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 
    select_list
FROM
    table_name
WHERE
    search_condition;

The search_condition is a combination of one or more predicates using the logical operator AND, OR and NOT.

In MySQL, a predicate is a Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN.

Any row from the table_name that causes the search_condition to evaluate to TRUE will be included in the final result set.

Besides the SELECT the statement, you can use the WHERE clause in the UPDATE or DELETE statement to specify which rows to update or delete.

In the SELECT statement, the WHERE a clause is evaluated after the FROM clause and before the SELECT clause.

MySQL WHERE clause examples

We’ll use the employees table from the sample database for the demonstration.

1) Using MySQL WHERE the clause with equal operator example

The following query uses the WHERE clause to find all employees whose job titles are Sales Rep:

SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep';

In this example, the SELECT the statement examines all rows of the employees table and selects only row whose value in the jobTitle column is Sales Rep.

2) Using MySQL WHERE clause with AND operator

The following example uses the WHERE clause to find employees whose job titles are Sales Rep and office codes are 1:

SELECT 
    lastname, 
    firstname, 
    jobtitle,
    officeCode
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND 
    officeCode = 1;

In this example, the expression in the WHERE clause uses the AND operator to combine two conditions:

jobtitle = 'Sales Rep' AND officeCode = 1;

The AND operator evaluates to TRUE only if both expressions evaluate to TRUE. Therefore, the query returns rows whose values in the jobTitle column is 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 
    lastName, 
    firstName, 
    jobTitle, 
    officeCode
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' OR 
    officeCode = 1
ORDER BY 
    officeCode , 
    jobTitle;

The OR operator evaluates to TRUE only if one of the expressions evaluates to TRUE:

jobtitle = 'Sales Rep' OR officeCode = 1

Therefore, the query returns any employee who has the job title Sales Rep or office code 1.

4) Using MySQL WHERE with BETWEEN operator example

The BETWEEN operator returns TRUE if a value is in a range of values:

expression BETWEEN low AND high

The following query finds employees who locate in offices whose office code is from 1 to 3:

SELECT 
    firstName, 
    lastName, 
    officeCode
FROM
    employees
WHERE
    officeCode BETWEEN 1 AND 3
ORDER BY officeCode;

5) Using MySQL WHERE with the LIKE operator example

The LIKE operator evaluates to TRUE if a value matches a specified pattern. To form a pattern, you use % and _ wildcards. The % wildcard matches any string of zero or more characters while the _ wildcard matches any single character.

This query finds employees whose last names end with the string 'son':

SELECT 
    firstName, 
    lastName
FROM
    employees
WHERE
    lastName LIKE '%son'
ORDER BY firstName;

6) Using MySQL WHERE clause with the IN operator example

The IN operator returns TRUE if a value matches any value in a list.

value IN (value1, value2,...)

The following example uses the WHERE clause with the IN operator to find employees who locate in the office with office code 1.

SELECT 
    firstName, 
    lastName, 
    officeCode
FROM
    employees
WHERE
    officeCode IN (1 , 2, 3)
ORDER BY 
    officeCode;

7) Using MySQL WHERE  clause with the IS NULL operator

To check if a value is NULLNULL or not, you use the IS NULL operator, not the equal operator (=). The IS NULL operator returns TRUE if a value is NULL.

value IS NULL

In the database world, NULL is a marker that indicates a piece of information is missing or unknown. It is not equivalent to the number 0 or an empty string.

This statement uses the WHERE clause with the IS NULL operator to get the row whose value in the reportsTo column is NULL:

SELECT 
    lastName, 
    firstName, 
    reportsTo
FROM
    employees
WHERE
    reportsTo IS NULL;

8) Using MySQL WHERE the 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.
<> or !=	Not equal to
<	Less than. You typically use it with numeric and date/time data types.
>	Greater than.
<=	Less than or equal to
>=	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 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees
WHERE
    jobtitle <> 'Sales Rep';

The following query finds employees whose office code is greater than 5:

SELECT 
    lastname, 
    firstname, 
    officeCode
FROM
    employees
WHERE 
    officecode > 5;

The following query returns employees with office code less than or equal 4 (<=4):

SELECT 
    lastname, 
    firstname, 
    officeCode
FROM
    employees
WHERE 
    officecode <= 4;

In this tutorial, you have learned how to use the MySQL WHERE clause to filter rows based on conditions.