Oracle / PLSQL: FROM Clause

This Oracle tutorial explains how to use the FROM clause in Oracle/PLSQL with syntax and examples.

Description

The Oracle/PLSQL FROM clause is used to list the tables and any join information required for the Oracle query.

Syntax

The syntax for the FROM clause in Oracle/PLSQL is:

FROM table1
[ { INNER JOIN
  | LEFT [OUTER] JOIN
  | RIGHT [OUTER] JOIN
  | FULL [OUTER] JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2

These are the tables used in the SQL statement. The two tables are joined based on table1.column1 = table2.column1.

Note

• There must be at least one table listed in the FROM clause in Oracle/PLSQL.
• If there are two or more tables listed in the FROM clause, these tables are generally joined in the FROM clause using INNER or OUTER joins. Although the tables can also be joined using the old syntax in the WHERE clause, we recommend using new standards and including your join information in the FROM clause. See Oracle joins for more information.

Example - With one table

It is difficult to explain the syntax for the Oracle FROM clause, so let's look at some examples.

We'll start by looking at how to use the FROM clause with only a single table.

For example:

SELECT *
FROM homes
WHERE bathrooms >= 2
ORDER BY home_type ASC;

In this Oracle FROM clause example, we've used the FROM clause to list the table called homes. There are no joins performed since we are only using one table.

Example - Two tables with INNER JOIN

Let's look at how to use the FROM clause with two tables and an INNER JOIN.

For example:

SELECT homes.home_id, customers.last_name, customers.first_name
FROM customers
INNER JOIN homes
ON customers.customer_id = homes.customer_id
ORDER BY home_id;

This Oracle FROM clause example uses the FROM clause to list two tables - customers and homes. And we are using the FROM clause to specify an INNER JOIN between the customers and homes tables based on the customer_id column in both tables.

Example - Two Tables with OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

For example:

SELECT customers.customer_id, contacts.last_name, contacts.first_name
FROM customers
LEFT OUTER JOIN contacts
ON customers.customer_id = contacts.contact_id
WHERE customers.last_name = 'Smith';

This Oracle FROM clause example uses the FROM clause to list two tables - customers and contacts. And we are using the FROM clause to specify a LEFT OUTER JOIN between the customers and contacts tables based on the customer_id column in both tables.