Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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(+);
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