Oracle SQL - Comparison Operators in the Joining Condition

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

Introduction

You can establish a relationship between a main query and its subquery by using one of the comparison operators (=, <, >, <=, >=, <>).

The subquery must return precisely one row.

These comparison operators are able to compare only a single left operand with a single right operand.

The following code shows how to use a Comparison Operator in the Joining Condition 

select ename, init, bdate 
from   emp 
where  bdate > (select bdate 
                from   emp 
                where  empno = 7011);

The query above returns all emp who are younger than employee 7011.

The subquery will never return more than one row, because EMPNO is the primary key of the EMPLOYEES table.

In case there is no employee with the employee number specified, you receive the "no rows selected" message.

The following code shows what would happen when the Subquery Returns No Rows 

select ename, init, bdate 
from   emp 
where  bdate > (select bdate 
                from   emp 
                where  empno = 99999); 
  
no rows selected

The subquery returning no rows, or producing an empty set is treated like a subquery returning one row instead, containing a null value.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w w  w.j av  a2s . c om

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

ENAME    | INIT  | BDATE
-------- | ----- | ---------
JACK     | JM    | 02-APR-77
SCOTT    | DEF   | 26-NOV-79
BREAD    | JJ    | 28-SEP-78
JONES    | R     | 03-OCT-79
FORD     | MG    | 13-FEB-79

SQL>

Related Topic