Understanding WHERE and ORDER BY

Let's delve a bit deeper into the SQL commands WHERE and ORDER BY.

Conditional Operators

First of all, we need to understand conditional operators. They are used in conjunction with the WHERE clause in SQL queries to filter data and retrieve specific records.

The following table explains the most common conditional operators:

The WHERE Command

The WHERE command is used to filter records in a table based on certain conditions. You can use WHERE to filter records based on a single column or multiple columns simultaneously.

In the example below, we are using WHERE on just one column, selecting only an ID that has been specified.

Syntax

In the next example, we're selecting all records with an age equal to 20 and 21.

Syntax

If you want to filter based on multiple columns, you can use the WHERE clause with the logical operators AND or OR.

Using the AND operator, the result will include all records that meet all the specified criteria. In the example below, all records with an age of 20 and 21 and residing in New York will be returned.

Syntax

Similarly, if you want to retrieve customers who are over 20 years old or are from the city of New York, you can use the OR operator.

Syntax

If you want to filter all records where the name starts with the letter L, for example, you can use the LIKE command.

Syntax

Notice that we only use the % after the letter L. This indicates that I want the first letter to be L, and the subsequent letters don't matter.

If I want all records that end with L, I would place the % before the L.

Syntax

The ORDER BY Command

We use the ORDER BY command when we want the result of our SELECT statement to be in a specific order. There are various ways to use this command.

In this first example, let's sort by age in ascending order.
Syntax

To sort by age in descending order:

Syntax

We can also sort by more than one column. For example, we can sort by age and then by date of birth.

Syntax