Join Queries  «Prev  Next»

Lesson 6 Using a query in the FROM clause
Objective Describe the parsing sequence of this kind of query.

Using Query in FROM Clause

Describe the parsing sequence of the FROM clause query in Oracle
In Oracle, the parsing sequence of a SQL query is essential to understand how the database processes the query to produce the desired result. The parsing sequence refers to the order in which the different clauses of a SQL query are executed. When executing a SQL query with a FROM clause, Oracle follows the following sequence:
  1. FROM clause: The FROM clause is the first step in the parsing sequence. In this step, Oracle identifies the base tables or views that will be used in the query. The data is then retrieved from these tables or views for further processing. This clause is essential because it determines the tables on which the query operates.
  2. WHERE clause: After identifying the base tables or views, Oracle applies the WHERE clause to filter the rows based on the specified conditions. The WHERE clause helps in narrowing down the result set by eliminating rows that do not meet the criteria.
  3. GROUP BY clause: If the query contains a GROUP BY clause, Oracle groups the rows that share the same values in the specified columns. It is used to aggregate data based on one or more columns.
  4. HAVING clause: Once the rows are grouped, the HAVING clause is applied to filter the grouped rows based on the specified condition. The HAVING clause is used in conjunction with the GROUP BY clause to filter the results after aggregation.
  5. SELECT clause: At this stage, Oracle selects the columns specified in the SELECT clause from the filtered and/or grouped rows. If the SELECT clause contains any expressions or functions, they are evaluated and calculated in this step.
  6. DISTINCT clause: If the query includes the DISTINCT keyword, Oracle removes duplicate rows from the result set. It ensures that each row in the result set is unique.
  7. ORDER BY clause: Finally, the ORDER BY clause is applied to sort the resulting rows based on the specified columns and sorting order (ASC or DESC). If no order is explicitly defined, the default is ASC (ascending).

The parsing sequence of a query with a FROM clause in Oracle is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY. Understanding this sequence is crucial for writing efficient and accurate SQL queries.

Oracle Parsing Sequence

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.

SELECT LASTNAME, DOLLARS
  FROM (SELECT LASTNAME,
  SUM(TOTAL_SALE_AMOUNT) DOLLARS
  FROM CUSTOMER C, CUSTOMER_SALE CS
  WHERE C.CUST_ID = CS.CUST_ID
    AND TAX_AMOUNT > 1
  GROUP BY LASTNAME) SUMOFSALES
WHERE DOLLARS > 50

From Clause Query - Exercise

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