Using FROM Clause in Oracle

This article is written about how to use the FROM clause in Oracle/PLSQL with syntax and examples.

Description

The Oracle/PLSQL FROM clause is used to listing the tables and any join facts 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 primarily based on table1.column1 = table2.column1.

Note

There need to be at least one desk listed in the FROM clause in Oracle/PLSQL. If there are two or more tables listed in the FROM clause, these tables are typically joined in the FROM clause the use of INNER or OUTER joins. Although the tables can also be joined using the old syntax in the WHERE clause, we suggest the usage of new standards and along with your be a part of records in the FROM clause. See Oracle joins for extra information.

Example – With one table

It is tough to give an explanation for the syntax for the Oracle FROM clause, so let’s look at some examples.

We’ll start by way of searching at how to use the FROM clause with solely a single table.

For example:

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

In this Oracle FROM clause example, we’ve got used the FROM clause to listing the table referred to as homes. There are no joins performed because we are only using one table.

Example – Two tables with INNER JOIN

Let’s seem 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 instance uses the FROM clause to listing two tables – customers and homes. And we are the usage of the FROM clause to specify an INNER JOIN between the clients and properties tables based totally on the customer_id column in both tables.

Example – Two Tables with OUTER JOIN

Let’s seem at how to use the FROM clause when we be a part of two tables together the use of an OUTER JOIN. In this case, we will seem 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 instance uses the FROM clause to list two tables – clients and contacts. And we are the usage of the FROM clause to specify a LEFT OUTER JOIN between the clients and contacts tables primarily based on the customer_id column in both tables.