Outer join Error

Adding the outer join operator on one side of the join (not both).


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(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, 5,             1006);


SQL> select e.employee_id, e.last_name, d.department_name
  2  from employees e, departments d
  3  where e.department_id(+) = d.department_id(+)
  4    and e.job_id = 'MGR';
where e.department_id(+) = d.department_id(+)
                         *
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table


SQL>

An outer join condition cannot be used with the IN operator:


SQL> select e.employee_id, e.last_name, d.department_name
  2  from employees e, departments d
  3  where e.department_id(+) IN (1,2,3)
  4    and e.job_id = 'MGR';
where e.department_id(+) IN (1,2,3)
                         *
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN


SQL>

An outer join condition cannot be used with another join connected with OR operator:


SQL> select e.employee_id, e.last_name, d.department_name
  2  from employees e, departments d
  3  where e.department_id = d.department_id(+)
  4    OR e.job_id = 'MGR';
where e.department_id = d.department_id(+)
                      *
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN


SQL>
SQL>
Home »
Oracle »
Select » 

Join:
  1. Table Join
  2. Using Table name to reference duplicate names
  3. Table Alias
  4. Cartesian Products
  5. Join more than two tables
  6. Join Conditions and Join Types
  7. Outer Joins
  8. Left and Right Outer Joins
  9. Outer join Error
  10. Self Join
  11. Outer Self Join
  12. Inner Joins Using SQL/92
  13. Joins with USING Keyword
  14. Inner Joins with More than Two Tables Using SQL/92
  15. Inner Joins on Multiple Columns Using SQL/92
  16. Outer Joins in SQL/92 Syntax
  17. Self Joins Using SQL/92
  18. Cross Joins Using SQL/92
Related: