Oracle SQL - Equijoins on Columns with the Same Name

  1. HOME
  2. Oracle SQL
  3. Table Join
  4. Equijoins

Introduction

SQL allows you to explicitly specify the columns to participate in the equijoin operation.

In the following code we use the ON clause followed by fully specified join predicates.

You can use the USING clause, specifying column names instead of full predicates. 

select e.ename, e.bdate
,      h.deptno, h.msal
from   emp e
       join
       history h
       using (empno)
where  e.job = 'ADMIN';

Demo

SQL>
SQL> drop table emp;

Table dropped.--   ww  w .ja  va 2s.co  m

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> drop table history;

Table dropped.

SQL> alter session  set NLS_DATE_FORMAT='DD-MM-YYYY';

Session altered.

SQL> create table history(
  2  empno      NUMBER(4)  ,
  3  beginyear  NUMBER(4)  ,
  4  begindate  DATE       ,
  5  enddate    DATE       ,
  6  deptno     NUMBER(2)  ,
  7  msal       NUMBER(6,2),
  8  comments   VARCHAR2(60)) ;
SQL>
SQL> insert into history values (7001,2000,'01-01-2000','01-02-2000',40, 950,'');
SQL> insert into history values (7003,2000,'01-02-2000', NULL       ,20, 800,'');
SQL> insert into history values (7002,1988,'01-06-1988','01-07-1989',30,1000,'');
SQL> insert into history values (7005,1989,'01-07-1989','01-12-1993',30,1300,'');
SQL>
SQL> select e.ename, e.bdate
  2  ,      h.deptno, h.msal
  3  from   emp e
  4         join
  5         history h
  6         using (empno)
  7  where  e.job = 'ADMIN';

no rows selected

SQL>

Related Topic