Oracle Inline Views
Version 19.2.0.1

Basic Inline View Select
Single Table SELECT <column_name_list>
FROM (
  <SELECT_statement>);
SELECT object_type, COUNT(*)
FROM all_objects
GROUP BY object_type
;

SELECT object_type
FROM (
  SELECT object_type, COUNT(*)
  FROM all_objects
  GROUP BY object_type
);

SELECT object_type
FROM (
  SELECT object_type, COUNT(*) OTCNT
  FROM all_objects
  GROUP BY object_type)

WHERE otcnt > 100;
 
Inline View with Join
Join In-line views SELECT <column_name_list>
FROM (
  <SELECT_statement>) <alias_1>
     (
  <SELECT_statement>) <alias_2>
WHERE alias_1.condition = alias_2.condition;
conn uwclass/uwclass@pdbdev

SELECT table_name, num_rows
FROM user_tables
;

SELECT index_name, table_name
FROM user_indexes
;

SELECT num_rows, index_name
FROM (SELECT table_name, num_rows
      FROM user_tables) a
,
     (SELECT index_name, table_name
      FROM user_indexes
) b
WHERE a.table_name = b.table_name(+);
 
Lateral Inline Views
Specify LATERAL to designate a subquery as a lateral inline view.

Within a lateral inline view, you can specify tables that appear to the left of the lateral inline view in the FROM clause of a query. You can specify this left correlation anywhere within subquery (such as the SELECT, FROM, and WHERE clauses) and at any nesting level.
SELECT <column_name_list>
FROM <[schema_name.]table_name>,
LATERAL (<inline_SQL_statement>)
[ORDER BY <column_list>];
conn scott/tiger@pdbdev

SELECT dname, ename
FROM scott.deptno d,
     (SELECT ename
      FROM scott.emp e
      WHERE e.deptno = d.deptno);
WHERE e.deptno = d.deptno)
*
ERROR at line 5:
ORA-00904: "D"."DEPT_ID": invalid identifier


SELECT dname, ename
FROM dept d,
LATERAL (SELECT ename
FROM emp e
WHERE e.deptno = d.deptno)
ORDER BY 1, 2;

DEPT_NAME      EMP_NAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
RESEARCH       SMITH
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES           WARD

Related Topics
Class Setup
Joins
SELECT Statement
WHERE Clause
VIEWS
What's New In 18cR3
What's New In 19cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
DBSecWorx