How to perform basic queries with `SELECT` in MySQL

Introduction

SELECT is the most suitable SQL command for when you are trying to query and return information inside of your MySQL tables. As its name implies, it is used to specify criteria used to select matching records from within the database. This is a broadly useful role that is suitable not only for reading data, but also for targeting updates and other actions.

In this article, we'll introduce the basic form of the SELECT command and demonstrate how to use it to return data. While SELECT supports many advanced use cases, we'll stick to some of the simpler forms to demonstrate the basic command structure.

The general syntax of the SELECT command

The basic format of a SELECT command looks something like this:

SELECT <column_names> FROM <table_name> <additional_conditions_and_formatting>;

This statement is composed of a few different pieces:

• SELECT: The SELECT command itself. This SQL command indicates that we want to query tables or views for data they contain. The arguments and clauses surrounding it determine both the contents and the format of the output returned.
• <column_names>: The SELECT statement can return entire rows (if specified with the * wildcard character) or a subset of the available columns. If you want to output only specific columns, provide the column names you'd like to display, separated by commas.
• FROM <table_name>: The FROM keyword is used to indicate the table or view that should be queried. In most simple queries, this consists of a single table that contains the data you're interested in.
• <additional_conditions_and_formatting>: A large number of filters, output modifiers, and conditions can be specified as additions to the SELECT command. You can use these to help pinpoint data with specific properties, modify the output formatting, or further process the results.

Specifying columns to display with SELECT

The column specification portion of the SELECT command requires you to name the columns you want to display for the data you are querying.

For ad hoc querying and during data exploration, one of the most helpful options is to use an asterisk to indicate that you want to display values from every column available:

SELECT * FROM my_table;

This will display all of the records from my_table since we do not provide any filtering to narrow the results. All of the columns for each record will be shown in the order that they are defined within the table.

One modification you may want to use if querying a table with many columns is to end your statement with \G instead of a semicolon ;:

SELECT * FROM my_table\G

The \G statement terminator tells MySQL to display the results vertically instead of horizontally, which can improve readability in tables with many columns or long values. You can use \G to terminate any statement, not just with SELECT.

You can also choose to view a subset of available column by specifying them by name. Column names are separated by commas and are displayed in the order in which they are given:

SELECT column2, column1 FROM my_table;

This will display all of the records from my_table, but only show the columns named column2 and column1, in that order.

Using column aliases with AS to modify the resulting table

You can optionally set column aliases to modify the name used for columns in the output.

SELECT column1 AS "first column" FROM my_table;

This will show the each of the values for column1 in my_table. However, the column in the output will be labeled as first column instead of column1.

This is especially useful if the output combines column names from multiple tables that might share names or if it includes computed columns that don't already have a name.

Defining sort order with ORDER BY

The ORDER BY clause can be used to sort the resulting rows according to the criteria given. The general syntax looks like this:

SELECT * FROM my_table ORDER BY <sort_expression>;

This will display the values for all columns in all records within my_table. The results will be ordered according to the expression represented by the placeholder <sort_expression>.

For example, suppose we have a customer table that contains columns for first_name, last_name, address, and phone_number. If we want to display the results in alphabetical order by last_name, we could use the following command:

SELECT * FROM customer ORDER BY last_name;

The results are sorted in ascending alphabetical order by the last_name column.

To reverse the ordering, we can add the DESC modifier to the end of the ORDER BY clause:

SELECT * FROM customer ORDER BY last_name DESC;

You can also sort by multiple columns. Here, we sort first by last_name, and then by first_name for any columns with the same last_name value. Both sorts are in ascending order:

SELECT * FROM customer ORDER BY last_name, first_name;

Getting distinct results

If you want to find the range of values for a column in MySQL, you can use the SELECT DISTINCT variant. This will display a single row for each distinct value of a column.

The basic syntax looks like this:

SELECT DISTINCT column1 FROM my_table;

This will show one row per unique value in column1.

For example, to display all of the different values for color that your shirt table contains, you can type:

SELECT DISTINCT color FROM shirt;

+--------+
  color  |
+--------+
  blue   |
  green  |
  orange |
  red    |
  yellow |
+--------+
5 rows in set (0.00 sec)

To show uniqueness across multiple columns, you can add additional columns separated by commas.

For instance, this will display all of the different combinations of color and shirt_size for the shirt table:

SELECT DISTINCT color,shirt_size FROM shirt;

+--------+------------+
  color  | shirt_size |
+--------+------------+
  blue   | M          |
  blue   | S          |
  green  | M          |
  green  | L          |
  green  | S          |
  orange | L          |
  orange | M          |
  red    | M          |
  yellow | S          |
+--------+------------+
9 rows in set (0.00 sec)

This displays every unique combination of color and shirt_size within the table.

Conclusion

In this article, we introduced some basic elements of the SELECT command to demonstrate how to return data from MySQL tables. There are many more optional clauses that modify the behavior of the command, allowing you to narrow down which results you want, specify the number of rows to return, and more. In later articles, we explore these modifiers to enhance the usefulness of SELECT.