Advanced SELECT statements

We can use many different clauses to change the behaviour of the SELECT statement. In this chapter we will describe and give examples of some common ones.

Display the number of rows in the table

We can use the COUNT() function to return the number of rows that matches a specified criteria. For example, to display the number of all rows in a table, we can use the following command:

mysql> SELECT * FROM testtb;
 +------+-------------+------+
 | name | surname | year |
 +------+-------------+------+
 | Amy | Goodridge | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 | John | Jones | 1985 |
 +------+-------------+------+
 4 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM testtb;
 +----------+
 | COUNT(*) |
 +----------+
 | 4 |
 +----------+
 1 row in set (0.00 sec)

Remove duplicate rows

Sometimes, when querying data from a table, you might get duplicate rows. To remove these duplicate rows you use the DISTINCT clause in the SELECT statement. For example, let’s say that we want a list of all first name in our table. If we select just the name column from a table, we will get duplicate results:

mysql> SELECT name FROM testtb;
 +------+
 | name |
 +------+
 | Amy |
 | Mark |
 | John |
 | John |
 +------+
 4 rows in set (0.00 sec)

Notice how the name John appears twice. To weed out multiple entries, we can use the DISTINCT clause:

mysql> SELECT DISTINCT name FROM testtb;
 +------+
 | name |
 +------+
 | Amy |
 | Mark |
 | John |
 +------+
 3 rows in set (0.01 sec)

Filter records

You can narrow down queries by returning only those where a certain expression is true. To do that, the WHERE clause is used. Here is the syntax:

SELECT column_name FROM table_name WHERE column_name operator value

Here is an example. Let’s say that we want to select all rows with the value John in the name column:

mysql> SELECT * FROM testtb;
 +------+-------------+------+
 | name | surname | year |
 +------+-------------+------+
 | Amy | Goodridge | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 | John | Jones | 1985 |
 +------+-------------+------+
 4 rows in set (0.00 sec)

mysql> SELECT * FROM testtb WHERE name='John';
 +------+-------------+------+
 | name | surname | year |
 +------+-------------+------+
 | John | von Neumann | 1921 |
 | John | Jones | 1985 |
 +------+-------------+------+
 2 rows in set (0.01 sec)

Here is another example. Let’s say that we want to select all rows where the year column is greater than 1980:

mysql> SELECT * FROM testtb WHERE year>1980;
  +------+-----------+------+
  | name | surname | year |
  +------+-----------+------+
  | Amy | Goodridge | 1991 |
  | John | Jones | 1985 |
  +------+-----------+------+
  2 rows in set (0.00 sec)