12.3 The SELECT Clause of the SQL Statement

SELECT is the first clause in a SQL statement. The SELECT clause is used to specify the columns you wish to see in a query result. Along with the FROM clause, the SELECT clause is required of every SQL statement that we write.

The Syntax of the SELECT Clause

The SELECT clause of a SQL statement always begins with the keyword SELECT (note that although the keywords in a SQL statement are not case sensitive, we will capitalize them in this lesson to highlight them). Immediately following the keyword SELECT in the SELECT clause, we list the names of the columns we wish to appear in the query result separated by commas. For example, if we wish to see the last name and email address of each of the members in the student organization data, the SQL statement for that query would be as follows:

SELECT last_name, email FROM Member

The results of this query are displayed in Figure 12.10.

You can choose to include or omit any of the columns from the Member table in your result. Additionally, the columns you include can appear in any order you wish. If you wanted the email address to appear before the last name in the result, you would write the query as follows:

SELECT email, first_name FROM Member

There is a shorthand way to include all columns from a table in a query. This is done using the * character instead of the list of column headings in the SELECT clause. This syntax is shown below and will produce the results in Figure 12.11. Notice that more columns are displayed in the query result than appear in the screenshot.

The data.world website contains additional instructions and examples for using the SELECT clause of SQL statements. You can access these materials at Basic Concepts: The SELECT and FROM Clauses.

Removing Duplicate Rows from a Query Result

You probably recall that every row of a database table is unique. This is not always the case with a query result. Notice that if we were to write a query to show only the position column from the Member table, many of the rows on the query result would contain duplicated information (see Figure 12.12).

The duplicated rows appear because multiple members hold the same position in the club. In this case, many members hold the position of “member.” There may be times that you will want to see this duplicated information. However, if the original intent of the query was to display a list of the different positions held by members, the duplicated rows could make it difficult to determine the unique positions.

We can rewrite the query to show only the unique rows in the query result (see Figure 12.13). To do this, we include the keyword DISTINCT after SELECT in the SELECT clause. Notice that there are only six unique positions held across the 33 members on the table. The updated syntax is below.

SELECT DISTINCT position FROM Member