SELECT CLAUSE

The SELECT clause is used to indicate which expressions to retrieve from the specified data source. Expressions can vary in complexity. Perhaps the least complex expressions are those involving pre-existing attributes. Pre-existing attributes are those attributes already captured within the data source. Other types of expressions include derived attributes, scalar values, and combinations of different types of expressions, among others. It can be difficult to understand the various types of expressions possible within the SELECT clause without concrete examples, so let’s walk through a few.

Before reading further, consider reviewing the Example Data section below to familiarize yourself with the data being used throughout the examples on this page. If you are already familiar with the data, then skip to the Pre-existing Attributes section

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Pre-existing Attributes
For the first example, let’s consider a scenario where the task is to generate a report containing the ID, first name, last name, and hire date of each employee. Based on the employees table above, each of these attributes are part of the table’s definition and are already being captured. As a result, we simply need to list those attributes within the SELECT clause and specify the data source or object containing the attributes of interest using the FROM clause. For the examples on this page, it is sufficient to simply know the FROM clause follows the expression list and is used to specify the data source. The code required to fulfill the task and the corresponding output can be found below.

SELECT
    hr.employees.employee_id,
    hr.employees.first_name,
    hr.employees.last_name,
    hr.employees.hire_date
FROM
    hr.employees;

Notice the commas present in the code above. When there is more than one expression, it is required to separate expressions with commas, leaving off the trailing comma. In the beginning of your SQL learning journey, it may be helpful to remember each SELECT clause requires one less comma than the number of expressions specified (i.e., n-1).

Derived Attributes
Derived attributes bridge the gap between the attributes stored in the data source and the attributes we need at any given time. Suppose, we are tasked with generating a report for people managers reminding them of important dates and milestones for each employee, such as the following:

– Benefits eligibility date (i.e., 30 days following the hire date)
– Ninety day review (i.e., 90 days following the hire date)
– One-year anniversary
– Five-year anniversary

These data points are not being captured, so we’ll need to derive them at run-time. To do so, we’ll take advantage of Oracle’s built-in function, ADD_MONTHS, and straightforward means to perform date arithmetic. The code required to fulfill the task and the corresponding output can be found below. To learn more about Oracle’s built-in datetime functions and date arithmetic through real-world examples, see my datetime functions page.

SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.hire_date + 30 AS benefits_eligible_date,
    hr.employees.hire_date + 90 AS ninety_day_review,
    ADD_MONTHS(hr.employees.hire_date, 12) AS one_year_anniversary,
    ADD_MONTHS(hr.employees.hire_date, 60) AS five_year_anniversay
FROM
    hr.employees;

Scalar Values
A task’s requirements may require you to incorporate scalar values (e.g., 1, 1.10, 100, etc.) into your solution. For example, suppose human resources would like you to generate a report displaying each employee’s salary. Additionally, they would like to see employees’ salaries with a three percent increase applied. To achieve this, simply multiply the pre-existing attribute, salary, by 1.03 using the ‘*’ symbol. For clarity, see the code and corresponding output below.

SELECT
    hr.employees.employee_id,
	hr.employees.salary,
	hr.employees.salary * 1.03 AS new_salary
FROM
	hr.employees;

Most mathematical operations or equations can be easily incorporated into your coding solutions using traditional operators (e.g., +, -, *, /). When performing division, ensure the output values do not deviate from the expected values. The handling of integer division varies among relational database management systems and “banker’s” rounding may occur.

Attribute Aliases
In the previous two examples, attributes were derived based on a combination of pre-existing attributes, scalar values, and the output of built-in functions. Using the AS keyword, a name for each derived attribute was provided. Use the AS keyword to provide meaningful names to derived attributes. If a name is not provided in the query, the attribute name will reflect its derivation. For example, the new_salary attribute above would read as “salary*1.03” in the absence of the AS keyword and a user-specified name.

Common Errors
One of the most common errors observe new SQL users make is including a trailing comma in the SELECT clause. When a comma follows the final expression specified in the SELECT clause, the relational database management system (RDBMS) will return an error. For Oracle, that error is ORA-00936: missing expression. If you remember the purpose of the commas in the SELECT clause is to separate the expressions listed, then this error message is rather helpful and makes sense. The RDBMS is expecting an expression to follow the comma!

Another error I frequently see new SQL users make is not including the necessary number of commas in the SELECT clause when more than one expression is specified. For example, suppose a query contains four expressions in the SELECT clause and the comma required after expression two is missing. When that query is executed, Oracle database will return the error, ORA-00923: FROM keyword not found where expected.

SQL veterans and new users alike make errors; the difference is experience in troubleshooting. Understanding a handful of common errors will allow you to jump to specific sections of your coding solution to resolve the errors quickly instead of requiring a more lengthy, top-down review.

Knowledge Check
If you’d like to confirm your understanding on the SELECT clause and other fundamental concepts, consider solving a handful of my practice problems here.