ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
