The difference between left Outer join and Outer Association (+) in Oracle

External Association is a proprietary statement of the Oracle database

The left Outer join is the standard statement of the SQL-92

These two SQL are generally considered to be equivalent, but there are some subtle differences.

In general, the equivalent condition of the outer association is equivalent to the ON statement in the left Outer join; the other statements in the two where are the same.

However, the other conditions in the left Outer join (non-table joins) appear after the ON keyword or appear after the WHERE keyword is not the same, such a statement is difficult to use the foreign association for synonymous escape.

Let's look at a test data, and a test case

Create a departmental table and employee table

CREATE TABLE Departments (depid number  (38,0), Depname VARCHAR2, Delflag number (1,0)); CREATE TABLE Employees (EmpID number  (38,0), EmpName VARCHAR2, depid number (38,0  ), Delflag number (1,0)); INSERT into departments values (1, ' finacle ', 0), insert into departments values (2, ' Marketing ', 0); INSERT INTO departments VALUES (3, ' HR ', 1); INSERT into Departments VALUES (4, ' IT ', 0); INSERT into employees values (1, ' WBQ ', 1,0), insert into employees values (2, ' Czh ', 2,0), insert into employees values (3, ' CHH ', 1,0); INSERT into employees values (4, ' Wal ', 2,0); INSERT into employees values (5, ' ddd ', 3,0); COMMIT;

Here are the test examples

--Lists department and employee information for Department ID 3, regardless of whether the department has an employee select D.depid,d.depname,e.empname from Departments D leave OUTER JOIN employees E on   D.depid = e.depid WHERE d.depid =3 ORDER by d.depid;--and above equivalent select D.depid,d.depname,e.empname from Departments D, employee S e WHERE d.depid = e.depid (+) and  d.depid =3 ORDER by D.depid;--This example may not be appropriate, d.depid=3 means to first make a full left connection and then find the depid=3 record if the department The corresponding employee is not found in, the department employee is null, and the department information is displayed. SELECT D.depid,d.depname,e.empname,d.delflag from Departments D left OUTER joins employees e on   d.depid = E.depid and E . depid=3 ORDER by d.depid;--and above are not equivalent, the difference is that there is no corresponding staff can be found in the department and the corresponding staff of the Department, but cannot find the department without employees. SELECT D.depid,d.depname,e.empname,d.delflag from Departments D, employees e WHERE d.depid = e.depid (+) and  (e.depid= 3 or E.depid is NULL) ORDER by D.depid;

　　

The difference between left Outer join and Outer Association (+) in Oracle