In this article, we will learn how we can sort and filter data using the WHERE clause and sort the data using the ORDER BY clause. In my previous article, Learn MySQL: Querying data from MySQL server using the SELECT statement, we learned how to generate the ER diagram using reverse-engineering the database using MySQL workbench and basics of SELECT statement and usage. Ins this article, we will learn how we can sort and filter data using the WHERE clause and sort the data using the ORDER BY clause.

Filtering data using the WHERE clause

To filter data, we use the WHERE clause in the SELECT query. The syntax is as follows:

Here, the condition can be combined with one or more than one logical expression. These logical expressions are called predicates. The predicates or logical expression can be evaluated as TRUE, FALSE, or UNKNOWN. When the WHERE clause does not return any row, then the predicated is evaluated as FALSE. If it returns the row, it will be included in the result-set, and the predicate is evaluated as TRUE.

To demonstrate, we are going to use the customer table of the sakila database. In this article, I will demonstrate various use cases of the WHERE clause and plan to cover the following use cases:

1. The WHERE clause with an equal operator
2. The WHERE clause with AND, OR, and BETWEEN operators
3. The WHERE clause with LIKE and IN operators
4. The WHERE clause with comparison operators

The WHERE clause with an equal operator

The following query populates the list of customers whose first name is ‘LINDA‘, to retrieve the records, the query should be written as follows:

The output is below:

In the query, the predicate is WHERE first_name = ‘LINDA ‘, which is evaluated as true.

The WHERE clause with AND operator

In this example, I will show how we can use multiple predicates to populate the desired records from the table. For example, we want to populate the customer whose address_id is 598 and store_id=1. To retrieve the records from the table, the query should be written as follows:

The following is the output:

Here we are including AND operator in the WHERE clause. The expression is WHERE address_id=598 and store_id=1. If both expressions are evaluated as true, then the entire expression is evaluated as TRUE. In the table, we have a record whose address_id=598 and store_id=1; therefore, the entire expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with OR operator

For example, we want to populate the customer whose store_id=1 OR active=0. To retrieve the records, the query should be written as follows:

The following is the output:

Here we are including OR operator in the WHERE clause. The expression is WHERE store_id=1 OR active=0. From both expressions, any one of them evaluated as true; then, the entire expression is evaluated as TRUE. In the table, we have a record whose store_id=1 OR active=0; therefore, the entire expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with BETWEEN operator

For example, we want to populate the customer whose address_id is between 560 and 570. To retrieve the records, the query should be written as follows:

The following is the output:

Here, we included the BETWEEN operator in the WHERE clause. The expression is WHERE address_id is BETWEEN 560 and 570. In the table, we have records where address_id is BETWEEN 560 and 570; therefore, the expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with IN operator

For example, we want to populate the customer whose customer_id in (10,11,12). To retrieve the records, the query should be written as follows:

The following is the output:

Here, we included the IN operator in the WHERE clause. The expression is WHERE customer_id in (10,11,12). In the table, we have a record whose customer_id in (10,11,12); therefore, the expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with a comparison operator

In the WHERE clause, we can specify the following comparison operators to match the values in the table. The details of the operators are as following:

For example, we want to populate the records whose address id is greater than 100; then the query should be written as follows:

Below is the output:

Suppose, we want to get the list of the inactive customers from the table. The query should be written as follows:

Below is the output.

Now, we want to populate the list of the customers who have been created after 12:47 PM on 22-07-2018. The query should be written as follows:

Below is the output:

Sorting data using ORDER BY clause

When a SQL query returns the output, the values are not sorted. To sort the result of a query, we use the ORDER BY clause. The syntax of the ORDER BY clause is the following:

When you specify ASC in the ORDER BY clause, the result will be sorted in ascending order, and if you specify DESC, then the result will be sorted in descending order.

The default sorting order is Ascending, so if we do not specify the sorting order, then the query result will be sorted in ascending order.

We can specify one or more than one column in the ORDER BY clause. The columns and their sorting order must be separated by comma (,). We can specify different sorting orders for the individual column. For example, if you want to sort the first column in ascending order and second column in descending order, then the syntax will be Column_1 ASC, column_2 desc. Here first, the column_1 will be sorted in ascending order and then the column_2 will be sorted in descending order. When the second column is sorted, the order of the values of the first column does not change.

For example, I want to see the list of customers in ascending order. The query should be written as follows:

The following is the screenshot of the output:

Another example, suppose I want to see the first name of the customer in ascending order and the last name in descending order then, the query should be written as follows:

The output is below:

We can use the ORDER BY clause to sort the result set that is generated by the expression. To demonstrate, I am going to use the rental table of the sakila database. Suppose the rental of the film has been increased. We want to check the updated price of the film, and the sorting must be performed on the updated rental. The query should be written as follows:

Below is the screenshot of the output:

Similarly, we can sort the output of the result-set generated by the arithmetic function. For example, I want to find the highest number of films available in the store. The query should be written as follows:

Below is the screenshot of the output:

Summary

In this article, we have learned to Sort and filter data generated by a query using the WHERE and ORDER BY clause. I have covered various use cases of the WHERE and ORDER BY clause. In the next article, we will learn about the INSERT statement to add data in the MySQL table and its various use cases with examples. Stay tuned..!!

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022