| 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 |
