Go to: Oracle connection Query

Select E. employee_id, E. last_name, D. department_id, D. department_name
From HR. Employees e left Outer Join HR. Orders ments d
On E. department_id = D. department_id



Select E. employee_id, E. last_name, D. department_id, D. department_name
From HR. Employees E, HR. Departments d
Where E. department_id = D. department_id (+)

-----------------
  
Select E. employee_id, E. last_name, D. department_id, D. department_name
From HR. Employees e right Outer Join HR. Orders ments d
On E. department_id = D. department_id


Select E. employee_id, E. last_name, D. department_id, D. department_name
From HR. Employees E, HR. Departments d
Where E. department_id (+) = D. department_id
-------------------------
  
Select E. employee_id, E. last_name, D. department_id, D. department_name
From HR. Employees e full outer join HR. Orders ments d
On E. department_id = D. department_id

-- Full connection is the union of left and right connections.
-- The inner connection is the intersection of the left and right connections.
 
Select count (0) from HR. Employees cross join HR. Orders-2889 records in total, Cartesian Product

Select count (0) from HR. Employees -- 107 records
Select count (0) from HR. Orders ments -- 27 Records

Select 107*27 from dual; -- 2889

-------------------------
Select * from HR. Employees natural join HR. Orders ments


Select E. employee_id, E. last_name, D. department_id, D. department_name
From HR. Employees e inner join HR. Orders ments d
On E. department_id = D. department_id

Select E. employee_id, E. last_name, department_id, D. department_name
From HR. Employees e inner join HR. Orders ments D using (department_id)

Before talking about external connections, let's give an example of internal connections, that is, normal equal connections.

Select * from a, B where a. ID = B. ID;

For external connections, "(+)" can be used in Oracle, and "left/right/full outer join" can be used in 9i. The following describes the external connections with instances.

1. left Outer Join: left Outer Join

select e.last_name, e.department_id, d.department_name from employees e left outer join departments 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.

2. Right outer join: Right Outer Join

select e.last_name, e.department_id, d.department_name from employees e right outer join departments 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.

3. Full outer join: Full outer join

select e.last_name, e.department_id, d.department_name from employees e full outer join departments 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.

Actually, the outer join is to query the left join of two tables, that is, all the values in the left table are available, and the values in the right table can be blank (+)

Right join is left Table value can be empty (+) Right Table value all have

Full join is the union of left join and right join. It seems to be this symbol (u)

The inner connection is the intersection of the left and right connections...

Other Connection Methods:

Cross join: cross join. The Cartesian Product eg: Select * from a cross join B (Table A has m rows and table B has n rows, the result is M * n rows ).

Left join

A. ID = B. ID (+) ==> all contents of Table A are displayed, which is based on the table on the left.

Left join: All tables on the left of left join are displayed. The table on the left is used as the benchmark;

Select * from EMP e left join dept D on E. deptno = D. deptno;

Right join a. ID (+) = B. ID => all contents of Table B are displayed, with the table on the right as the reference.

Right join: All tables on the Right of right join are displayed. The right table is used as the reference.

Select * from EMP e right join dept D on E. deptno = D. deptno;

Right join and left join do not have where from statements.

Note: "+" indicates the right connection on the left of "=" and "+" indicates the left connection on the right.

Natural join: Natural join

Natural join, natural join will automatically create a connection according to the list, omitting the where statement to avoid the emergence of Cartesian Product

Eg: Select empno, ename, Sal, deptno, LOC from EMP natural join dept;

Using: associate with the specified column.

Eg: Select E. ename, E. Sal, deptno, D. LOC from EMP e join dept D using (deptno) Where deptno = 20;

Note: The column referenced by the using clause cannot use the table name or alias as the prefix in any part of the SQL statement.

PS: when external join is performed, the where condition cannot be added with the slave table condition. You need to make a subquery of the slave table into another table.

Eg: Select T. acctype, nvl (B. Name, T. acctype) as name
From biacciccardmaptb T, dictcodesettb B
Where B. Category = 'account type'
And T. acctype = B. Code (+)
And T. iccardno = '000000' this is wrong

Shocould:

Select T. acctype, nvl (B. Name, T. acctype) as name
From biacciccardmaptb T, (select * From dictcodesettb where category = 'account type') B
Where
T. acctype = B. Code (+)
And T. iccardno = '000000'

Http://bqsongning.blog.163.com/blog/static/115471473200932284753219/