Select Clause

Select Clause Introduction

The SELECT clause is used to specify the columns that we want to retrieve from the table. The SELECT clause is the most important clause in SQL. It is used to fetch the data from a database table which returns data in the form of a result table.

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

Query 1

SELECT *
FROM department;

Query 2

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

SELECT dept_id, name
FROM department;

Query 3

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

SELECT name
FROM department;

The job of the select clause, therefore, is the following:

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

Query 4

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 by including in your select clause such things 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 employee table:

SELECT emp_id,
'ACTIVE',
emp_id * 3.14159,
UPPER(lname)
FROM employee;

Column Aliases

The column alias is an optional clause that allows you to rename a column or expression in the result set.

You can do so by adding a column alias after each element of your select clause. Here’s the previous query against the employee table with column aliases applied to three of the columns:

Query 5

SELECT emp_id,
'ACTIVE' status,
emp_id * 3.14159 empid_x_pi,
UPPER(lname) last_name_upper
FROM employee;

Query 6

If you look at the select clause, you can see how the column aliases status, empid_x_pi, and last_name_upper are added after the second,third, and fourth columns.

SELECT emp_id,
'ACTIVE' AS status,
emp_id * 3.14159 AS empid_x_pi,
UPPER(lname) AS last_name_upper
FROM employee;

Removing Duplicates

In some cases, a query might return duplicate rows of data. For example, if you were to retrieve the IDs of all customers that have accounts, you would see the following:

Query 7

SELECT cust_id
FROM account;

Query 8

Since some customers have more than one account, you will see the same customer ID once for each account owned by that customer. What you probably want in this case is the distinct set of customers that have accounts, instead of seeing the customer ID for each row in the account table. You can achieve this by adding the keyword distinct directly after the select keyword, as demonstrated by the following:

SELECT DISTINCT cust_id
FROM account;