Oracle SQL - Subqueries in the SELECT Clause

  1. HOME
  2. Oracle SQL
  3. Subquery
  4. Subquery

Introduction

The following code shows how to use Subqueries in the SELECT Clause.

The query returns the number of emp in each department. 

select d.deptno, d.dname, d.location,
     (select count(*)
       from   emp e
       where  e.deptno = d.deptno) as emp_count
from   departments d;

Demo

SQL>
SQL> drop table emp;

Table dropped.--   www  .  java  2s .com

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> select d.deptno, d.dname, d.location,
  2       (select count(*)
  3         from   emp e
  4         where  e.deptno = d.deptno) as emp_count
  5  from   departments d;

   DEPTNO | DNAME      | LOCATION | EMP_COUNT
--------- | ---------- | -------- | ---------
    1     | ACCOUNTING | NEW YORK |  00005.00
    2     | TRAINING   | DALLAS   |  00005.00
    30    | SALES      | CHICAGO  |  00004.00
    4     | HR         | BOSTON   |  00000.00

4 rows selected.

SQL>

Related Topic