How to filter query results in MySQL

Examples using WHERE

One of the most common and straightforward checks is for equality, using the = operator. Here, we check whether each row in the customer table has a last_name value equal to Smith:

SELECT * FROM customer WHERE last_name = 'Smith';

We can add additional conditions to this to create compound expressions using logical operators. This example uses the AND clause to add an additional test against the first_name column. Valid rows must satisfy both of the given conditions:

SELECT * FROM customer WHERE first_name = 'John' AND last_name = 'Smith';

Similarly, we can check whether any of a series of conditions are met. Here, we check rows from the address table to see whether the zip_code value is equal to 60626 or the neighborhood column is equal to the string "Roger's Park":

SELECT * FROM address WHERE zip_code = '60626' OR neighborhood = "Roger's Park";

The IN operator can work like an comparison between a number of values, wrapped in parentheses. If there is a match with any of the given values, the expression is TRUE:

SELECT * FROM customer WHERE last_name IN ('Smith', 'Johnson', 'Fredrich');

Here, we check against a string pattern using LIKE. The % works as a wildcard matching zero or more characters, so "Pete", "Peter", and any other string that begins with "Pete" would match:

SELECT * FROM customer WHERE last_name LIKE 'Pete%';

We could do a similar search using the REGEXP function to check for matches using regular expressions. In this case, we check whether the value of last_name begins with a "d" and contains the substring "on", which would match names like "Dickson", "Donald", and "Devon":

SELECT * FROM customer WHERE last_name REGEXP '^D.*on.*';

We can check whether a street number is within the 4000 block of addresses using the BETWEEN and AND operators to define an inclusive range:

SELECT * FROM address WHERE street_number BETWEEN 4000 AND 4999;

Here, we can display any customer entries that have social security numbers that are not 9 digits long. We use the LENGTH() function to get the number of digits in the field and the <> to check for inequality:

SELECT * FROM customer WHERE LENGTH(SSN) <> 9;

Examples using GROUP BY

For the examples in this section, suppose that we have a table called pet that we've defined and populated like so:

CREATE TABLE pet (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    name VARCHAR(50),
    color VARCHAR(50),
    age INT
);

INSERT INTO pet (type, name, color, age) VALUES
('dog', 'Spot', 'brown', 3),
('dog', 'Rover', 'black', 7),
('dog', 'Sally', 'brown', 1),
('cat', 'Sabrina', 'black', 8),
('cat', 'Felix', 'white', 4),
('cat', 'Simon', 'orange', 8),
('rabbit', 'Buttons', 'grey', 4),
('rabbit', 'Bunny', 'brown', 8),
('rabbit', 'Briony', 'brown', 6);

The simplest use of GROUP BY is to display the range of unique values for a single column. To do so, use the same column in SELECT and GROUP BY. Here, we see all of the colors used in the table:

SELECT color FROM pet GROUP BY color;

+--------+
  color  |
+--------+
  brown  |
  black  |
  white  |
  orange |
  grey   |
+--------+
5 rows in set (0.00 sec)

As you move beyond a single column in the SELECT column list, you must either add the columns to the GROUP BY clause or use an aggregate function to produce a single value for the group of rows being represented.

Here, we add type to the GROUP BY clause, meaning that each row will represent a unique combination of type and color values. We also add the age column, summarized by the avg() function to find the average age of each of the groups:

SELECT type, color, avg(age) AS average_age FROM pet GROUP BY type, color;

+--------+--------+-------------+
  type   | color  | average_age |
+--------+--------+-------------+
  dog    | brown  |      2.0000 |
  dog    | black  |      7.0000 |
  cat    | black  |      8.0000 |
  cat    | white  |      4.0000 |
  cat    | orange |      8.0000 |
  rabbit | grey   |      4.0000 |
  rabbit | brown  |      7.0000 |
+--------+--------+-------------+
7 rows in set (0.00 sec)

Aggregate functions work just as well with a single column in the GROUP BY clause. Here, we find the average age of each type of animal:

SELECT type, avg(age) AS average_age FROM pet GROUP BY type;

+--------+-------------+
  type   | average_age |
+--------+-------------+
  dog    |      3.6667 |
  cat    |      6.6667 |
  rabbit |      6.0000 |
+--------+-------------+
3 rows in set (0.00 sec)

If we want to display the oldest of each type of animal, we could instead use the max() function on the age column. The GROUP BY clause collapses the results into the same rows as before, but the new function alters the result in the other column:

SELECT type, max(age) AS oldest FROM pet GROUP BY type;

+--------+--------+
  type   | oldest |
+--------+--------+
  dog    |      7 |
  cat    |      8 |
  rabbit |      8 |
+--------+--------+
3 rows in set (0.00 sec)

Examples using HAVING

Using the same table we introduced in the last section, we can demonstrate how the HAVING clause works.

Here, we group the rows of the pet table by unique values in the type column, finding the minimum value of age as well. The HAVING clause then filters the results to remove any groups where the age is not greater than 1:

SELECT type, min(age) AS youngest FROM pet GROUP BY type HAVING min(age) > 1;

+--------+----------+
  type   | youngest |
+--------+----------+
  cat    |        4 |
  rabbit |        4 |
+--------+----------+
2 rows in set (0.00 sec)

In this example, we group the rows in pet by their color. We then filter the groups that only represent a single row. The result shows us every color that appears more than once:

SELECT color FROM pet GROUP BY color HAVING count(color) > 1;

+-------+
  color |
+-------+
  brown |
  black |
+-------+
2 rows in set (0.00 sec)

We can perform a similar query to get the combinations of type and color that only a single animal has:

SELECT type, color FROM pet GROUP BY type, color HAVING count(color) = 1;

+--------+--------+
  type   | color  |
+--------+--------+
  dog    | black  |
  cat    | black  |
  cat    | white  |
  cat    | orange |
  rabbit | grey   |
+--------+--------+
5 rows in set (0.00 sec)

Examples using LIMIT

We will use the pet table from earlier for the examples in this section.

As mentioned above, LIMIT is often combined with an ORDER BY clause to explicitly define the ordering of the rows before slicing the appropriate number. Here, we sort the pet entries according to their age, from oldest to youngest. We then use LIMIT to display the top 5 oldest animals:

SELECT * FROM pet ORDER BY age DESC LIMIT 5;

+----+--------+---------+--------+------+
  id | type   | name    | color  | age  |
+----+--------+---------+--------+------+
   4 | cat    | Sabrina | black  |    8 |
   6 | cat    | Simon   | orange |    8 |
   8 | rabbit | Bunny   | brown  |    8 |
   2 | dog    | Rover   | black  |    7 |
   9 | rabbit | Briony  | brown  |    6 |
+----+--------+---------+--------+------+
5 rows in set (0.00 sec)

If we need a record for any single dog within the table, we could construct a query like this. Keep in mind that while the result might be difficult to predict, this is not a random selection and should not be used as such:

SELECT * FROM pet WHERE type = 'dog' LIMIT 1;

+----+------+------+-------+------+
  id | type | name | color | age  |
+----+------+------+-------+------+
   1 | dog  | Spot | brown |    3 |
+----+------+------+-------+------+
1 row in set (0.00 sec)

We can use the OFFSET clause to paginate through results. We include an ORDER BY clause to define a specific order for the results.

For the first query, we limit the results without specifying an OFFSET to get the first 3 youngest entries:

SELECT * FROM pet ORDER BY age LIMIT 3;

+----+--------+---------+-------+------+
  id | type   | name    | color | age  |
+----+--------+---------+-------+------+
   3 | dog    | Sally   | brown |    1 |
   1 | dog    | Spot    | brown |    3 |
   7 | rabbit | Buttons | grey  |    4 |
+----+--------+---------+-------+------+
3 rows in set (0.00 sec)

To get the next 3 youngest, we can add the number defined in LIMIT to the OFFSET to skip the results we've already retrieved:

SELECT * FROM pet ORDER BY age LIMIT 3 OFFSET 3;

+----+--------+---------+-------+------+
  id | type   | name    | color | age  |
+----+--------+---------+-------+------+
   7 | rabbit | Buttons | grey  |    4 |
   9 | rabbit | Briony  | brown |    6 |
   2 | dog    | Rover   | black |    7 |
+----+--------+---------+-------+------+
3 rows in set (0.00 sec)

If we add the LIMIT to the OFFSET again, we'll get the next 3 results:

SELECT * FROM pet ORDER BY age LIMIT 3 OFFSET 6;

+----+--------+---------+--------+------+
  id | type   | name    | color  | age  |
+----+--------+---------+--------+------+
   4 | cat    | Sabrina | black  |    8 |
   6 | cat    | Simon   | orange |    8 |
   8 | rabbit | Bunny   | brown  |    8 |
+----+--------+---------+--------+------+
3 rows in set (0.00 sec)

This lets us retrieve rows of data from a query in manageable chunks.