Oracle table join method (illustrated)

Normal equal connections:
Select * from a, B where a. ID = B. ID;
This is an internal connection.

For external connections:
"(+)" Can be used in Oracle, and 9i can use left/right/full outer join.

Left Outer Join: left Outer Join
Select E. last_name, E. department_id, D. department_name
From employees e
Left Outer Join orders ments d
On (E. department_id = D. department_id );
Equivalent
Select E. last_name, E. department_id, D. department_name
From employees e, departments d
Where E. department_id = D. department_id (+)
Result: Records of all employees and their respective departments, including records of employees who do not have the corresponding department number department_id.

Right outer join: Right Outer Join
Select E. last_name, E. department_id, D. department_name
From employees e
Right Outer Join orders ments d
On (E. department_id = D. department_id );
Equivalent
Select E. last_name, E. department_id, D. department_name
From employees e, departments d
Where E. department_id (+) = D. department_id
Result: Records of all employees and their respective departments, including records of departments without any employees.

Full outer join: Full outer join
Select E. last_name, E. department_id, D. department_name
From employees e
Full outer join orders ments d
On (E. department_id = D. department_id );
Result: Records of all employees and their respective departments, including employee records without the corresponding department number department_id and department records without any employees.

Oracle8i is a syntax that does not directly support full outer join. That is to say, you cannot add (+) to both the left and right tables. The following is the complete Outer Join syntax that can be referenced in Oracle8i.
Select t1.id, t2.id from Table1 T1, table T2 where t1.id = t2.id (+)
Union
Select t1.id, t2.id from Table1 T1, table T2 where t1.id (+) = t2.id

Connection Type	Definition	Illustration	Example
Internal Connection	Connect only matched rows		Select a. C1, B. C2 from a join B on A. C3 = B. C3;
Left Outer Join	Contains all rows in the left table (no matter whether the table on the right has rows that match them) and all matched rows in the right table		Select a. C1, B. C2 from a left join B on A. C3 = B. C3;
Outer right connection	Contains all rows in the right table (no matter whether the table on the left has rows that match them) and all matched rows in the left table		Select a. C1, B. C2 from a right join B on A. C3 = B. C3;
All external connections	Contains all rows in the left and right tables, regardless of whether the tables on the other side have rows matching them.		Select a. C1, B. C2 from a full join B on A. C3 = B. C3;
(Theta) Connection	Use conditions other than equivalence to match rows in the left and right tables		Select a. C1, B. C2 from a join B on A. C3! = B. C3;
Cross join	Generate Cartesian Product -- it does not use any matching or selection conditions, but directly matches each row in a data source with each row in another data source.		Select a. C1, B. C2 from A, B;