SQL SELECT Statement

Even though the select clause is the first clause of a select statement, it is one of the last clauses that the database server evaluates. The reason for this is that before you can determine what to include in the final result set, you need to know all of the possible columns that could be included in the final result set. In order to fully understand the role of the select clause, therefore, you will need to understand a bit about the from clause. Here’s a query to get started.

SELECT * 
FROM language;

In this query, the from clause lists a single table (language), and the select clause indicates that all columns (designated by *) in the language table should be included in the result set. This query could be described in English as follows:

Show me all the columns and all the rows in the language table.

In addition to specifying all the columns via the asterisk character, you can explicitly name the columns you are interested in, such as:

SELECT language_id, name, last_update 
FROM language;

The results are identical to the first query, since all the columns in the language table (language_id, name, and last_update) are named in the select clause. You can choose to include only a subset of the columns in the language table as well:

SELECT name
FROM language;

The job of the select clause, therefore, is as follows:

The select clause determines which of all possible columns should be included in the query’s result set.

If you were limited to including only columns from the table or tables named in the from clause, things would be rather dull. However, you can spice things up in your select clause by including things such as:

Literals, such as numbers or strings

Expressions, such as transaction.amount * −1

Built-in function calls, such as ROUND(transaction.amount, 2)

User-defined function calls

The next query demonstrates the use of a table column, a literal, an expression, and a built-in function call in a single query against the language table:

SELECT 
    language_id,
    'COMMON' language_usage,
    language_id * 3.1415927 lang_pi_value,
    upper(name) language_name
FROM language;

If you only need to execute a built-in function or evaluate a simple expression, you can skip the from clause entirely. Here’s an example:

SELECT 
    version(),
    user(),
    database();

Since this query simply calls three built-in functions and doesn’t retrieve data from any tables, there is no need for a from clause.

Column Aliases –

Although the mysql tool will generate labels for the columns returned by your queries, you may want to assign your own labels. While you might want to assign a new label to a column from a table (if it is poorly or ambiguously named), you will almost certainly want to assign your own labels to those columns in your result set that are generated by expressions or built-in function calls. You can do so by adding a column alias after each element of your select clause. Here’s the previous query against the language table, which included column aliases for three of the columns:

SELECT 
    language_id,
    'COMMON' language_usage,
    language_id * 3.1415927 lang_pi_value,
    upper(name) language_name
FROM language;

If you look at the select clause, you can see how the column aliases language_usage, lang_pi_value, and language_name are added after the second, third, and fourth columns. I think you will agree that the output is easier to understand with column aliases in place, and it would be easier to work with programmatically if you were issuing the query from within Java or Python rather than interactively via the mysql tool. In order to make your column aliases stand out even more, you also have the option of using the as keyword before the alias name, as in:

SELECT 
    language_id,
    'COMMON' AS language_usage,
    language_id * 3.1415927 AS lang_pi_value,
    upper(name) AS language_name
FROM language;

Many people feel that including the optional as keyword improves readability.

Removing Duplicates –

In some cases, a query might return duplicate rows of data. For example, if you were to retrieve the IDs of all actors who appeared in a film, you would see the following:

SELECT actor_id
FROM film_actor
ORDER BY actor_id;

Since some actors appeared in more than one film, you will see the same actor ID multiple times. What you probably want in this case is the distinct set of actors, instead of seeing the actor IDs repeated for each film in which they appeared. You can achieve this by adding the keyword distinct directly after the select keyword, as demonstrated by the following:

SELECT distinct actor_id
FROM film_actor
ORDER BY actor_id;

The result set now contains 200 rows, one for each distinct actor, rather than 5,462 rows, one for each film appearance by an actor.