Joins with USING Keyword

SQL/92 simplifies the join condition with the USING clause with the following limitations:

The query must use an equijoin. The columns in the equijoin must have the same name.


CREATE TABLE departments
(department_id             number(10)            not null,
 department_name           varchar2(50)      not null,
 CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

insert into departments ( department_id, department_name )values(1,'Data Group' );
insert into departments ( department_id, department_name )values(2,'Purchasing' );
insert into departments ( department_id, department_name )values(3,'Call Center' );
insert into departments ( department_id, department_name )values(4,'Communication' );

CREATE TABLE employees
( employee_id          number(10)      not null,
  last_name            varchar2(50)      not null,
  job_id               varchar2(30),
  department_id        number(10),
  salary               number(6),
  manager_id           number(6),
  CONSTRAINT           employees_pk PRIMARY KEY (employee_id),
  CONSTRAINT           fk_departments FOREIGN KEY (department_id) 
                       REFERENCES departments(department_id)
);
insert into employees( employee_id, last_name, job_id, salary,department_id ,manager_id)
values(                1001,        'Lawson',  'MGR',  30000, 1,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1002,        'Wells',   'DBA',  20000, 2,             1005 );
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1003,        'Bliss',   'PROG', 24000, 3,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1004,        'Kyte',    'MGR',  25000 ,4,             1005);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1005,        'Viper',   'PROG', 20000, 1,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id,manager_id)
values(                1006,        'Beck',    'PROG', 20000, 2,             null);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1007,        'Java',    'PROG', 20000, 3,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1008,        'Oracle',  'DBA',  20000, 4,             1006);


SQL> select employee_id, last_name, department_name
  2  from employees INNER JOIN departments
  3  Using (department_id)
  4  ;

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_NAME
----------- --------------------------------------------------
       1001 Lawson                    Data Group
       1002 Wells                     Purchasing
       1003 Bliss                     Call Center
       1004 Kyte                      Communication
       1005 Viper                     Data Group
       1006 Beck                      Purchasing
       1007 Java                      Call Center
       1008 Oracle                    Communication


8 rows selected.

SQL>

With Using keyword style, table alias is not allowed in the select statement for the joining column


SQL> select employee_id, last_name, department_name,departments.department_id
  2  from employees INNER JOIN departments
  3  Using (department_id)
  4  ;
select employee_id, last_name, department_name,departments.department_id
                                               *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier


SQL>
SQL> select e.employee_id, e.last_name, d.department_name,d.department_id
  2  from employees e INNER JOIN departments d
  3  Using (department_id)
  4  ;
select e.employee_id, e.last_name, d.department_name,d.department_id
                                                     *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier


SQL>
SQL>