Oracle SELECT Statement

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

In Oracle, tables are consists of columns and rows. For example, the customers table in the sample database has the following columns: customer_id, name, address, website and credit_limit. The  customers table also has data in these columns.

To retrieve data from one or more columns of a table, you use the SELECT statement with the following syntax:

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.
• Second, indicate the columns from which you want to return the data. If you have more than one column, you need to separate each by a comma (,).

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.

Oracle SELECT examples

Let’s take some examples of using the Oracle SELECT statement to understand how it works.

A) query data from a single column

To get the customer names from the customers table, you use the following statement:

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

The following picture illustrates the result:

B) Querying data from multiple columns

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

The following example shows how to query 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)

The following shows the result:

C) Querying data from all columns of a table

The following example retrieves all rows from all columns of the customers table:

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

Here is the result:

To make it handy, you can use the shorthand asterisk (*) to instruct Oracle to return data from all columns of a table as follows:

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

Note that you should never use the asterisk (*) when you embed the query in applications. It is a good practice to explicitly specify the columns from which you want to query data even when you want to retrieve data from all columns of a table. You should the asterisk (*) shorthand for ad-hoc queries only.

This is because a table may have more or fewer columns in the future due to business changes. If you use the asterisk (*) in the application code and assume that the table has a fixed set of columns, the application may either not process the additional columns or access the removed columns.

In this tutorial, you have learned how to use Oracle SELECT statement to retrieve data from one or more columns of a table.