How To Use Subqueries in the FROM Clause in Oracle?

If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. The following statement shows you how to use a subquery as base table for the main query:

SQL> SELECT * FROM (
  2    SELECT first_name, last_name, department_name
  3    FROM employees e, departments d 
  4    WHERE e.department_id = d.department_id
  5  ) WHERE department_name LIKE 'S%' ORDER BY last_name;
FIRST_NAME        LAST_NAME              DEPARTMENT_NAME
----------------- ---------------------- ---------------
Ellen             Abel                   Sales
Sundar            Ande                   Sales
Mozhe             Atkinson               Shipping
Amit              Banda                  Sales
Elizabeth         Bates                  Sales
Sarah             Bell                   Shipping
......

⇒ Counting Groups Returned with the GROUP BY Clause in Oracle

⇐ Use Subqueries with the EXISTS Operator in Oracle

⇑ Understanding SQL SELECT Query Statements in Oracle

⇑⇑ Oracle Database Tutorials