Oracle SQL - Single-Row Subquery Using a Comparison Operator

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

Introduction

The following code shows how you can retrieve all emp who are younger than colleague 7004. 

select e.empno, e.ename, e.init, e.bdate
from   emp e
where  e.bdate > (select x.bdate
                  from   emp x
                  where  x.empno = 7004);

Here, the code uses a single-row subquery.

The subquery must return a single row, because the comparison operator (>) of the outer query would fail otherwise.

Demo

SQL>
SQL>-- from   w  ww .  j  a v  a  2 s . c  o m
SQL> drop table emp;

Table dropped.

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>
SQL> select e.empno, e.ename, e.init, e.bdate
  2  from   emp e
  3  where  e.bdate > (select x.bdate
  4                    from   emp x
  5                    where  x.empno = 7004);

    EMPNO | ENAME    | INIT  | BDATE
--------- | -------- | ----- | ----------
 07008.00 | SCOTT    | DEF   | 26-11-1979
 07010.00 | BREAD    | JJ    | 28-09-1978
 07012.00 | JONES    | R     | 03-10-1979
 07902.00 | FORD     | MG    | 13-02-1979

SQL>

Related Topic