FROM CLAUSE

The FROM clause is used to indicate the object from which the expressions specified in the SELECT clause are sourced. Like expressions in the SELECT clause, objects specified in the FROM clause vary in complexity. The most straightforward and common object type found in the FROM clause is a pre-existing table. These tables and corresponding attributes are already defined in a database schema, allowing a user to retrieve data from them by simply specifying the required table in the FROM clause. The focus of this page is to illustrate how the FROM clause can be used by walking through a handful of real-world examples where the object type(s) referenced in the FROM clause vary. For those object types requiring a deeper understanding, I’ll provide a high-level description of the object type and a real-world example. For an in-depth exploration and understanding of those object types, I highly recommend reading the corresponding page(s) dedicated to each. Each of the more complex object types referenced on this page affords you some benefit or functionality when solving real-world problems. Understanding how and when to use them will be invaluable to you.

Before reading further, consider reviewing the Example Data section below to familiarize yourself with the data being used throughout the examples on this page. If you are already familiar with the data, then skip to the Pre-existing Tables section.

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Pre-existing Tables
Retrieving data from these tables is easy. Simply specify the table in the FROM clause. In the example code below, a report is generated containing the customer ID, first name, last name, and email address of each customer. The corresponding output is provided following the code.

SELECT
	oe.customers.customer_id,
    oe.customers.cust_first_name,
    oe.customers.cust_last_name,
    oe.customers.cust_email
FROM
	oe.customers;

As you continue reading, remember, this page is dedicated to providing a high-level overview on the several ways the FROM clause can be used and the expectation is for those reading this page to be at the beginning of their SQL learning journey. Knowledge of how to source data from pre-existing tables is the foundation you should be building at this point. The objects and concepts covered throughout the rest of this page are crucial to learning SQL and using it to solve real-world problems, efficiently; however, a firm understanding on these can wait until all BIG SIX clauses are well-understood. Continue reading to further explore other objects commonly used in the FROM clause.

Derived Tables
A derived table or inline view is the result set generated by a subquery. Subqueries are particularly useful in solving problems containing multiple steps or subproblems and are surrounded by parentheses. In a similar fashion to mathematics, a subquery is executed prior to the outer query relying on its output. Subqueries can be used within several clauses, including SELECT, FROM, WHERE, and HAVING, among others. For better or worse, they can also be nested. The query below contains an example of a perfectly valid subquery, albeit a superfluous one.

SELECT
    subquery_example.order_id,
    subquery_example.order_date,
    subquery_example.customer_id,
    subquery_example.order_total
FROM (
    SELECT
    	oe.orders.order_id,
        oe.orders.order_date,
        oe.orders.customer_id,
        oe.orders.order_total
    FROM
    	oe.orders
) subquery_example;