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.

customers table

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;

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 that consists of many clauses such as ORDER BYGROUP BYHAVINGJOIN. 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;

The following picture illustrates the result:

Oracle SELECT - Select one column

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;

The following shows the result:

Oracle SELECT - Select from multiple columns

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;

Here is the result:

Oracle SELECT - customers table

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;

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 the 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.

Was this tutorial helpful?