Lesson 4. Filtering and Sorting Data

In this lesson, you learn how to add a WHERE clause to a SELECT statement to filter the retrieved data, and how to use an ORDER BY clause to sort the query results.

The WHERE Clause

You can add a WHERE clause to a SELECT statement to tell MySQL to filter the query results based on a given rule. Rules in a WHERE clause refer to data values returned by the query, and only rows in which the values meet the criteria in the rule are returned.

Filtering on an Exact Value

The simplest type of filter in a WHERE clause uses the equals operator ( = ) to specify that a data value must match a given value exactly.

The following query retrieves all the contacts for a given company:

mysql>

SELECT id, first_name, last_name

->

FROM customer_contacts

->

WHERE customer_code = 'SCICORP';

+----+------------+-----------+
 id  first_name  last_name 
+----+------------+-----------+
  4  Albert      Einstein  
  5  Charles     Darwin    
  6  Marie       Curie     
  7  Benjamin    Franklin  
+----+------------+-----------+
4 rows in set (0.03 sec)

In this example, rows from customer_contacts are returned only if the value of customer_code in that row is equal to SCICORP .

You often use an exact match operator in a WHERE clause to return all the columns in a table when you know the primary key of a record. For instance, to find all the information about a product from its unique product code, you could use the following query:

mysql>

SELECT *

->

FROM products

->

WHERE code = 'MIDI';

+------+----------------+--------+-------+
 code  name            weight  price 
+------+----------------+--------+-------+
 MIDI  Medium product    4.50   9.99 
+------+----------------+--------+-------+
1 row in set (0.00 sec)

To perform a condition using an inequality, use the( = )operator. This works just the same as =, but the condition returns only rows in which the table value is not equal to the value given in the condition.

For instance, the following query finds all products except for MINI :

mysql>

SELECT *

->

FROM products

->

WHERE code(

=

)'MINI';

+------+----------------+--------+-------+
 code  name            weight  price 
+------+----------------+--------+-------+
 MIDI  Medium product    4.50   9.99 
 MAXI  Large product     8.00  15.99 
+------+----------------+--------+-------+
2 rows in set (0.02 sec)s

Filtering on a Range of Values

In addition to the equality operator, you can use a set of operators to select rows based on a range of values. Use the symbols < and > to test whether one value is less than or greater than another value, respectively.

When followed by an = symbol, these operators also match equal values. The symbol sequence >= means "is greater than or equal to," whereas <= means "is less than or equal to."

To find only products for which the price is .99 or lower, use the following query:

mysql>

SELECT *

->

FROM products

->

WHERE price <= 9.99;

+------+----------------+--------+-------+
 code  name            weight  price 
+------+----------------+--------+-------+
 MINI  Small product     1.50   5.99 
 MIDI  Medium product    4.50   9.99 
+------+----------------+--------+-------+
2 rows in set (0.00 sec)

This example uses the <= operator, meaning that rows in which the price value is less than or equal to 9.99 are returned. If you use the < operator instead, the MIDI product will not be returned by the query because it costs exactly .99.

The range operators can be performed on textual data, and the results are logical. For example, performing a greater-than comparison on the last_name field returns only values that are alphabetically higher than the given value.

mysql>

SELECT last_name

->

FROM customer_contacts

->

WHERE last_name > 'G';

+-----------+
 last_name 
+-----------+
 Lincoln   
 Nixon     
 Roosevelt 
 Gershwin  
 Lennon    
+-----------+
5 rows in set (0.00 sec)

Word Comparisons Comparing text strings in MySQL works like the ordering of words in a dictionary. The word sandwich would appear after the word sand in the dictionary because it is a longer word. Similarly MySQL would consider sandwich to be a greater value than sand .

Using Quotes Around Values

You might have noticed in the previous examples that sometimes the values used in a WHERE clause were contained in single quotes. This is necessary when the value being compared is a non-numeric value because MySQL needs to know whether you are referring to a fixed value or a column name from a table in the query.

Consider the following query, which produces the error shown:

mysql>

SELECT last_name

->

FROM customer_contacts

->

WHERE first_name = Benjamin;

ERROR 1054 (42S22): Unknown column 'Benjamin' in 'where clause'

The error message indicates that MySQL is trying to find a column named Benjamin in the customer_contacts tableof course, this does not exist. To tell MySQL that a value is not a column name, you must enclose it in quotes, as shown:

mysql>

SELECT last_name

->

FROM customer_contacts

->

WHERE first_name = 'Benjamin';

+-----------+
 last_name 
+-----------+
 Franklin  
 Britten   
+-----------+
2 rows in set (0.00 sec)

Although the usual convention in a WHERE clause is WHERE column = ' value ', this ordering is not significant. The following query demonstrates that the order can be reversed without affecting the outcome:

mysql>

SELECT last_name

->

FROM customer_contacts

->

WHERE 'Benjamin' = first_name;

+-----------+
 last_name 
+-----------+
 Franklin  
 Britten   
+-----------+
2 rows in set (0.00 sec)

Number Values Quotes are not needed around number values. MySQL does not get confused with numeric comparisons because a column name cannot begin with a number.

MySQL does enable you to perform a query using two columns, although this is rarely useful with two columns from the same table. A slightly peculiar example is shown here:

mysql>

SELECT first_name, last_name

->

FROM customer_contacts

->

WHERE first_name = last_name;

Empty set (0.00 sec)

This query attempts to find names from the customer_contacts table whose first name and last name are the same. Such names are unusualthe query will find only names such as Scott Scott or Thomas Thomasso no rows are returned from the sample tables. Note that this query does not find matches for Benjamin Franklin and Franklin Rooseveltit compares only the first_name and last_name values from a single row of data.

Comparing Column Values Having two different column names on either side of the = character in a WHERE clause is an important SQL technique for queries that use more than one table. You will learn how to do this in Lesson 11, "Joining Tables."