Inline View

View All Scripts Login to Run Script

Script Name Inline View
Description use a select statement inline in a query column list
Area 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.

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

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.

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

Additional Information

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