Describe the parsing sequence of this kind of query.
Using Query in FROM Clause
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:
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