Summary: In this chapter, we’ll learn how to use WHERE clause in the SELECT statement to filter rows data.

Filter Rows Using MySQL WHERE

Introduction to MySQL WHERE clause

If we use the SELECT statement to fetch data from tables without the WHERE clause, we will get all rows data in the result set that may be not necessary. It does not make sense to get all rows data from a table especially for big tables like employees, department, etc., because we often want to analyze a set of data at a time.

The WHERE clause is used to extract only those records that fulfill a specified criterion. Simply We can say WHERE clause just filter the data based on condition.

SQL WHERE Syntax

SELECT
   column_name,column_name
FROM
   table_name
WHERE
   column_name operator VALUE;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator	Description
=	Equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column
IS NULL	checks if the value is NULL.

The WHERE clause is used not only with the SELECT statement but also other SQL statements to filter rows such as DELETE and UPDATE.

MySQL WHERE clause examples

We will continue with the employees table data in the sample database as shown in the picture below.

selectall_query

Ex 1:

Suppose, we just want to get employees data who is Software Engineer from the employees table, we’ll use the following query:

SELECT
  firstname, lastname, jobtitle, salary
FROM
  employees
WHERE
  jobtitle = 'Software Engineer';

where_equal

Ex 2:

We can form a simple condition like the query for Ex 1, or a very complex one that combines multiple expressions with logical operators such as AND, OR, etc. For example, to find all employees data who is Software Engineer and whose salary is 25000, We’ll use the following query:

SELECT
  firstname, lastname, jobtitle, salary
FROM
  employees
WHERE
  jobtitle = 'Software Engineer' AND salary = 25000;

where_and

Ex 3:

The following query just to get all employees data who are not the Software Engineer:

SELECT
  firstname, lastname, jobtitle, salary
FROM
  employees
WHERE
  jobtitle <> 'Software Engineer';

where_notequal

Ex 4:

The following query gets every employee whose salary is greater than 30000:

SELECT
  firstname, lastname, jobtitle, salary
FROM
  employees
WHERE
  salary > 30000;

where_greaterthan

In this chapter, we have learnt various ways of using MySQL WHERE clause such as based on different different condition.