Oracle SELECT Statement

Summary: In this tutorial, you will learn how to use the Oracle SELECT statement to query data from a single table.

Introduction to Oracle SELECT statement #

In Oracle, tables consist of columns and rows. For example, the customers table in the sample database has the following columns:

• customer_id
• name
• address
• website
• credit_limit

To retrieve data from one or more columns of a table, you use the SELECT statement.

Here’s the basic syntax of the SELECT statement:

SELECT
  column_1, 
  column_2, 
  ...
FROM
  table_name;Code language: SQL (Structured Query Language) (sql)

In this SELECT statement:

• First, specify the table name from which you want to query the data in the FROM clause.
• Second, indicate the columns from which you want to return the data in the SELECT clause. To retrieve data from multiple columns, separate them with a comma (,).

When executing the SELECT statement, Oracle evaluates the FROM before the SELECT clause.

Note that the SELECT statement is very complex and consists of many clauses, such as ORDER BY, GROUP BY, HAVING, JOIN. To make it simple, in this tutorial, we are focusing on the SELECT and FROM clauses only.

Querying data from a single column #

The following example uses the SELECT statement to retrieve data from the name column of the customers table:

SELECT
  name
FROM
  customers;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

How the query works:

• First, Oracle retrieves data from customers table.
• Second, Oracle returns only values in the name column specified in the SELECT clause.

Querying data from multiple columns #

To query data from multiple columns, you specify a list of comma-separated column names in the SELECT clause.

For example, the following SELECT statement retrieves data from the customer_id, name, and credit_limit columns of the customer table:

SELECT
  customer_id,
  name,
  credit_limit
FROM
  customers;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

How it works:

• First, retrieve data from the customers table.
• Second, return data from the customer_id, name, and credit_limit columns.

Querying data from all columns of a table #

The following SELECT statement retrieves data from all columns of the customers table:

SELECT
  customer_id,
  name,
  address,
  website,
  credit_limit
FROM
  customers;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Alternatively, you can use the star (*) shorthand to return all columns of a table without specifying them explicitly:

SELECT
  *
FROM
  customers;Code language: SQL (Structured Query Language) (sql)

Try it

This query is known as the SELECT star statement.

The select star is convenient when you want to form ad-hoc queries to examine all the columns of tables.

However, you should never use the asterisk (*) when you embed the query in applications for performance purposes. The reason is that the application may only need data from some columns, not all.

Column aliases #

A column alias is a temporary column name that you assign to a column during the execution of a query.

Here’s the syntax for assigning a column an alias:

column_name AS alias_nameCode language: SQL (Structured Query Language) (sql)

Since the AS keyword is optional, you can remove it like this:

column_name alias_nameCode language: SQL (Structured Query Language) (sql)

For example, you can assign a column alias customer_name to the name column of the customers table as follows:

SELECT name AS customer_name
FROM customers;Code language: SQL (Structured Query Language) (sql)

Try it

Using expressions in the SELECT clause #

When querying data, you can transform it using an expression. For example, you can increase the credit limit to all customers by 10% like this:

SELECT
  name,
  credit_limit,
  credit_limit * 1.1 new_credit_limit
FROM
  customers;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

In this example, we use the SELECT statement to retrieve data from the name and credit_limit columns of the customers table.

We also transform the values in the credit_limit column by multiplying it with 1.1 (10%).

Since we want to display the new credit limit with a more meaningful name, we use a column alias new_credit_limit.

Summary #

• Use the SELECT statement to retrieve data from a table.
• Oracle evaluates the FROM clause before the SELECT clause.
• Use the SELECT * to retrieve data from all columns of a table without explicitly specifying them in the SELECT clause.
• A column alias is a temporary name assigned to a column during query execution.

Quiz #