Inline View Subquery in Oracle

Inline View Subquery in Oracle with Examples

In this article, I am going to discuss Inline View Subquery in Oracle with Examples. Please read our previous article where we discussed Pseudo Columns in Oracle with Examples.

What is Inline View Subquery in Oracle?

It is special type of subquery in Oracle. Providing a select query in place of table name in select statement. The following is the syntax.
SELECT * FROM <TABLE NAME>; — SQL SELECT QUERY
SELECT * FROM (<SELECT QUERY>); –INLINE VIEW

Note:
  1. Generally, Subquery is not allowed to use “order by” clause. So that we use “inline view”.
  2. Generally, Column alias names are not allowed to use in “where” clause Condition. So that we use “inline view”.
Example to understand Inline View Subquery in Oracle

We are going to use the following Employee table to understand Inline View Subquery in Oracle with Examples.

Example to understand Inline View Subquery in Oracle

Please use the below SQL Script to create and populate the Employee table with the required sample data.

CREATE TABLE EMPLOYEE
(	
        EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2)
);

INSERT INTO EMPLOYEE VALUES (7369, 'SMITH', 'CLERK',      7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMPLOYEE VALUES (7521, 'WARD', 'SALESMAN',    7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMPLOYEE VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMPLOYEE VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
Using column alias names in where clause condition:

WAQ to display employees whose annual salary is more than 25000?
Solution:
SELECT * FROM (SELECT ENAME, SAL, SAL*12 ANNUSAL FROM EMPLOYEE) WHERE ANNUSAL>25000;
Output:

Using column alias names in where clause condition

Using the “ORDER BY” clause in a subquery:

WAQ to display the first five highest salaries from Employee table by using ROWNUM along with inline view?
Solution:
SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SAL DESC) WHERE ROWNUM<=5;
Output:

Using the "ORDER BY" clause in a subquery

Example:

WAQ to display 5th highest salary of employee from Employee table by using ROWNUM along with inline view?
Solution:
SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SAL DESC) WHERE ROWNUM<=5
MINUS
SELECT * FROM (SELECT * FROM EMPLOYEE ORDER BY SAL DESC) WHERE ROWNUM<=4;
Output:

Inline View Subquery in Oracle

Using ROWNUM alias name in oracle:

WAQ to display 3rd position row from Employee table by using ROWNUM alias name along with inline view?
Solution:
SELECT * FROM (SELECT ROWNUM R, ENAME, JOB, SAL FROM EMPLOYEE) WHERE R=3;
(OR)
SELECT * FROM (SELECT ROWNUM R, EMPLOYEE.* FROM EMPLOYEE) WHERE R=3;
Output:

Using ROWNUM alias name in oracle

Example:

WAQ to display 1st,3rd,5th,7th,9th rows from employee table by using ROWNUM alias name along with inline view?
Solution:
SELECT * FROM (SELECT ROWNUM R, EMPLOYEE.* FROM EMPLOYEE) WHERE R IN(1,3,5,7,9);
Output:

Inline View Subquery in Oracle with Examples

Example:

WAQ to display even position rows from employee table by using ROWNUM Alias name along with inline view?
Solution:
SELECT * FROM (SELECT ROWNUM R, EMPLOYEE.* FROM EMPLOYEE) WHERE MOD(R,2)=0;
Output:

Inline View Subquery in Oracle with Examples

Example:

WAQ to display first row and last row from employee table by using ROWNUM alias name along with inline view?
Solution:
SELECT * FROM (SELECT ROWNUM R, EMPLOYEE.* FROM EMPLOYEE) WHERE R=1 OR R=(SELECT COUNT(*) FROM EMPLOYEE);
Output:

Oracle Inline View Subquery

In the next article, I am going to discuss Analytical Functions in Oracle with examples. Here, in this article, I try to explain Inline View Subquery in Oracle with Examples. I hope you enjoy this Oracle Inline View Subquery article.

Leave a Reply

Your email address will not be published.