SELECT clause queries – MySQL Beginner Basics Series.

Coming full circle in the MySQL Beginner Basics Series, this blog post will highlight simple SELECT queries for retrieving and reading the data in tables.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.


Blog Posts In The Series

For an overview of the series as of the time of this writing, check out the list of posts below.

  1. CREATE Database, User, and Table – Easy examples with MySQL
  2. ALTER TABLE: Examples with MySQL – Beginner Series
  3. Populating a MySQL table with the INSERT statement – Beginner Series.
  4. UPDATE and DELETE: Examples with MySQL – Beginner Series

Sample Database and Table

In this blog post I will be using a mock HR database and in particular, the countries table found therein.
This database can easily be downloaded from many resources on the web, including the MySQL version in this post, and installed on your system.

All the present tables are listed below.

mysql> SHOW tables;
+--------------+
| Tables_in_hr |
+--------------+
| countries    |
| departments  |
| employees    |
| job_history  |
| jobs         |
| locations    |
| regions      |
+--------------+
7 rows in set (0.00 sec)

And a description of the countries table I will be querying:

mysql> DESC countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | NO   | PRI | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  | MUL | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.18 sec)

SELECT Basics

A basic SELECT query often used is the, ‘select everything’ query, as shown below:

mysql> SELECT * FROM countries;
+------------+--------------------------+-----------+
| COUNTRY_ID | COUNTRY_NAME             | REGION_ID |
+------------+--------------------------+-----------+
| AR         | Argentina                |         2 |
| AU         | Australia                |         3 |
| BE         | Belgium                  |         1 |
| BR         | Brazil                   |         2 |
| CA         | Canada                   |         2 |
| CH         | Switzerland              |         1 |
| CN         | China                    |         3 |
| DE         | Germany                  |         1 |
| DK         | Denmark                  |         1 |
| EG         | Egypt                    |         4 |
| FR         | France                   |         1 |
| HK         | HongKong                 |         3 |
| IL         | Israel                   |         4 |
| IN         | India                    |         3 |
| IT         | Italy                    |         1 |
| JP         | Japan                    |         3 |
| KW         | Kuwait                   |         4 |
| MX         | Mexico                   |         2 |
| NG         | Nigeria                  |         4 |
| NL         | Netherlands              |         1 |
| SG         | Singapore                |         3 |
| UK         | United Kingdom           |         1 |
| US         | United States of America |         2 |
| ZM         | Zambia                   |         4 |
| ZW         | Zimbabwe                 |         4 |
+------------+--------------------------+-----------+
25 rows in set (0.03 sec)

The * character denotes all in this context. It is saying return everything from the table there is, in the query results.

Another common SELECT query, retrieves a total count of records found in the table.
This is a handy query to run when exploring new tables you are unfamiliar with.

mysql> SELECT COUNT(*) FROM countries;
+----------+
| COUNT(*) |
+----------+
|       25 |
+----------+
1 row in set (0.02 sec)

Cool. The countries table has 25 records.

You can also restrict how many records are returned by appending the LIMIT clause to the end of aSELECT query like so.

mysql> SELECT *
    -> FROM countries
    -> LIMIT 5;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
| AR         | Argentina    |         2 |
| AU         | Australia    |         3 |
| BE         | Belgium      |         1 |
| BR         | Brazil       |         2 |
| CA         | Canada       |         2 |
+------------+--------------+-----------+
5 rows in set (0.00 sec)

The syntax LIMIT some_number only returns at most, that many records and can possibly return less. (ie.. You query a table, limiting the result set to just 5 rows, but the table has only 4 rows to begin with.)

The real power of SELECT is the ability to list out specific column names you want to be returned.
Below I fetch just the COUNTRY_NAME column.

mysql> SELECT COUNTRY_NAME
    -> FROM countries
    -> LIMIT 5;
+--------------+
| COUNTRY_NAME |
+--------------+
| Argentina    |
| Australia    |
| Belgium      |
| Brazil       |
| Canada       |
+--------------+
5 rows in set (0.00 sec)

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!

Be sure and visit the “Best Of” page for a collection of my best blog posts.


However, you can list out multiple columns. Just separate them with commas as shown below.

mysql> SELECT COUNTRY_ID, COUNTRY_NAME
    -> FROM countries
    -> LIMIT 3;
+------------+--------------+
| COUNTRY_ID | COUNTRY_NAME |
+------------+--------------+
| AR         | Argentina    |
| AU         | Australia    |
| BE         | Belgium      |
+------------+--------------+
3 rows in set (0.00 sec)

Mixed order in a SELECT query is fine too,

mysql> SELECT COUNTRY_NAME, COUNTRY_ID
    -> FROM countries
    -> LIMIT 3;
+--------------+------------+
| COUNTRY_NAME | COUNTRY_ID |
+--------------+------------+
| Argentina    | AR         |
| Australia    | AU         |
| Belgium      | BE         |
+--------------+------------+
3 rows in set (0.00 sec)

From the above examples, you can surmise that the common syntax for SELECT queries is:

SELECT column_name or column_names FROM table_name;

Distinct Columns

You may have noticed the REGION_ID field has several identical values.
Oftentimes, you may want only one value returned, where there are many alike.
The DISTINCT modifier basically tosses out duplicate rows.
Have a look.

mysql> SELECT REGION_ID FROM countries;
+-----------+
| REGION_ID |
+-----------+
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         2 |
|         2 |
|         2 |
|         2 |
|         2 |
|         3 |
|         3 |
|         3 |
|         3 |
|         3 |
|         3 |
|         4 |
|         4 |
|         4 |
|         4 |
|         4 |
|         4 |
+-----------+
25 rows in set (0.00 sec)

“What are the different region id’s to choose from?” you wonder.

mysql> SELECT DISTINCT REGION_ID
    -> FROM countries;
+-----------+
| REGION_ID |
+-----------+
|         1 |
|         2 |
|         3 |
|         4 |
+-----------+
4 rows in set (0.06 sec)

In the above SELECT query, DISTINCT returns non-duplicates.
Now you know what region_id’s are in the table to choose from.


One-Offs and Ad Hoc Queries

You can run a quick ad-hoc SELECT to perform calculations on the fly as well.
Some addition,

mysql> SELECT 10 + 2;
+--------+
| 10 + 2 |
+--------+
|     12 |
+--------+
1 row in set (0.02 sec)

or returning an all-uppercase version of a name using the UPPER() function.

mysql> SELECT UPPER('josh');
+---------------+
| UPPER('josh') |
+---------------+
| JOSH          |
+---------------+
1 row in set (0.04 sec)

Explore More

This blog post is just a quick introduction to simple SELECT queries. SELECT is a power-house of a clause and is considered the workhorse of SQL. SELECT queries can be rich, complex, and super specific.
I encourage you to explore the official MySQL 5.7 Online Manual for in-depth study, clarification, and options.


A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!

Be sure and visit the “Best Of” page for a collection of my best blog posts


Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Leave a Reply