MySQL WHERE

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

1 2 3 4 5 6

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.

Ex 1:

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

1 2 3 4 5 6

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

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:

1 2 3 4 5 6

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

Ex 3:

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

1 2 3 4 5 6

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

Ex 4:

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

1 2 3 4 5 6

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

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

---

MySQL DISTINCT MySQL AND

---