Using Query in FROM Clause

Here's a breakdown of the general parsing sequence of the FROM clause in an Oracle SQL query:

1. Table Identification and Aliasing:
   • Oracle identifies the tables or views specified in the FROM clause.
   • If an alias is provided after a table/view name (e.g., `FROM Customers C`), the alias is assigned for referencing the table throughout the query.
2. Join Processing (if applicable):
   • Join Type Evaluation: Oracle determines the join types (INNER JOIN, LEFT OUTER JOIN, etc.) indicated in the query.
   • Join Conditions: It analyzes the conditions specified in the `ON` clause and establishes how the tables should be linked based on matching columns.
   • Join Order: The Oracle optimizer often has flexibility in join ordering and chooses a plan it believes will be the most efficient. This can be influenced by statistics, optimizer hints, and other factors.
3. WHERE Clause Filtering:
   • After tables are joined (if applicable), conditions from the `WHERE` clause are applied. This filters out rows that don't meet the specified criteria.
4. GROUP BY and HAVING Clauses (if applicable):
   • Grouping: `GROUP BY` is applied, consolidating rows based on grouping columns.
   • Filtering Groups: The `HAVING` clause, if present, filters out entire groups based on specified conditions.

Important Notes

• Optimizer Influence: The actual parsing sequence might be adjusted by the Oracle optimizer to improve performance, based on database statistics and the overall query structure.
• Subqueries: Subqueries in the FROM clause are processed as nested datasets, with their own parsing and filtering occurring within their scope.

Example

SELECT C.Name, SUM(O.OrderTotal) as total_spent
FROM Customers C 
JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE O.OrderDate > '2023-04-01'
GROUP BY C.Name;

In this example, Oracle would:

1. Identify `Customers` (aliased as `C`) and `Orders` (aliased as `O`).
2. Process the join condition linking them on the `CustomerID`.
3. Filter rows in `Orders` based on the `WHERE` clause.
4. Group the results by `C.Name` and calculate the sum.

An unusual type of query construction can be used to create a virtual table to be used in your query. This is not used very often because the same results can usually be generated using a simpler query. This type of query is used more often when you begin working with Oracle's nested tables. You can pull an entire nested table out of an object using this technique. You will revisit this concept in a later module of this course when we cover querying Oracle objects.

• FROM clause syntax
  The basic syntax of this kind of query is:
  

  SELECT col1, col2, ...FROM (query) alias WHERE ...
  

  
  The type of query used in the FROM clause varies:
  1. If you are using standard SQL only, the query must either return a single row or use SELECT * in the SELECT clause. This limits the usefulness of this kind of query.
  If you are using Oracle, you can place any query you want inside the parentheses. This makes this query construction much more flexible. When Oracle executes a query that contains another query in the FROM clause, it first executes the query in the FROM clause and builds a virtual table containing the results. Then it executes the main query using the virtual table. The following graphic shows an example of a query that uses a subquery in place of a table in the FROM clause. You can see the subquery starting in line 2 and ending in line 7 of the query. The subquery has been given a table alias name: SUMOFSALES. The SELECT clause in the first line lists columns that are found in the SELECT clause of the subquery.
  The next lesson concludes this module.

Click the Exercise link below to practice writing some queries.
From Clause Query - Exercise