Inline View

View All Scripts Login and Run Script

Script Name Inline View
Description use a select statement inline in a query column list
Category SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

Note the select statement used in the list of columns.

select  
    "DEPARTMENT_ID",  
    "DEPARTMENT_NAME",  
    (select count(*) from hr.employees e where e.department_id = d.department_id) employee_count  
from HR.DEPARTMENTS d

DEPARTMENT_ID	DEPARTMENT_NAME	EMPLOYEE_COUNT
10	Administration	1
20	Marketing	2
30	Purchasing	6
40	Human Resources	1
50	Shipping	45
60	IT	5
70	Public Relations	1
80	Sales	34
90	Executive	3
100	Finance	6
110	Accounting	2
120	Treasury	0
130	Corporate Tax	0
140	Control And Credit	0
150	Shareholder Services	0
160	Benefits	0
170	Manufacturing	0
180	Construction	0
190	Contracting	0
200	Operations	0
210	IT Support	0
220	NOC	0
230	IT Helpdesk	0
240	Government Sales	0
250	Retail Sales	0
260	Recruiting	0
270	Payroll	0

27 rows selected.

Statement 2

Creating a view will highlight how inline view query optimization

create or replace view hr_departments 
as 
select  
    "DEPARTMENT_ID",  
    "DEPARTMENT_NAME",  
    (select count(*) from hr.employees e where e.department_id = d.department_id) employee_count  
from HR.DEPARTMENTS d

View created.

Statement 3
```
select department_Name, employee_count 
from hr_departments 
where employee_count > 0 
order by employee_count desc
```
DEPARTMENT_NAME EMPLOYEE_COUNT
Shipping 45
Sales 34
Finance 6
Purchasing 6
IT 5
Executive 3
Accounting 2
Marketing 2
Public Relations 1
Administration 1
Human Resources 1

11 rows selected.

DEPARTMENT_NAME	EMPLOYEE_COUNT
Shipping	45
Sales	34
Finance	6
Purchasing	6
IT	5
Executive	3
Accounting	2
Marketing	2
Public Relations	1
Administration	1
Human Resources	1

Statement 4

The select count(*) query will not be executed because it is not selected from the view. The optimizer will automatically remove the columns and not perform the select against the employee table.

select department_Name 
from hr_departments 
order by department_Name desc

DEPARTMENT_NAME
Treasury
Shipping
Shareholder Services
Sales
Retail Sales
Recruiting
Purchasing
Public Relations
Payroll
Operations
NOC
Marketing
Manufacturing
IT Support
IT Helpdesk
IT
Human Resources
Government Sales
Finance
Executive
Corporate Tax
Control And Credit
Contracting
Construction
Benefits
Administration
Accounting

27 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials