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

Statement 3

select department_Name, employee_count
from hr_departments
where employee_count > 0
order by employee_count desc

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

Additional Information

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