Talkingically, the most commonly used connection: internal connection, external connection, left external connection, right external connection
The above severings are generally used in multitious queries,
Let's talk about the internal connections, which is equal, such as two tables dept, Employee,
There are field deptids, deptname, Employee, DEPLOYEE, have field Empno, DEPTID, ENAME, SAL, AGE, etc.
The connection query statement is as follows: SELECT A. *, B. * From dept a, employee b where a.deptid = b.deptid
This is the case where this is a primary table, the other is a sub-table, the primary key of the primary table as a sub-table, the display result is simple in the display, the internal connection is simple, here is not more
Outside connection in orract ( ), first look
Select Empno, ENAME, SAL, Emp.deptno, Dept.deptno from Emp, Dept Where Emp.Deptno ( ) = Dept.deptno
This is a right connection
See the example below
Select Empno, ENAME, SAL, Emp.deptno, Dept.deptno from Emp, Dept Where dept.no = Emp.Deptno ( )
This is a left connection
The results of the query displayed by the two queries are the same. When I started learning Oracle, many people are hard to understand, and now I have a stupid approach.
Everyone does not prevent it, ( ) The left side of the query conditions appears to be right, appears on the right, the left connection. ( ) Can be placed on the left.
Placed on the right, but must be placed on the side of the missing conditions, as in the above example, if the company is recently prepared to set up a new department,
The test department, but now there is no new employee, and now BOSS wants to know that the company knows the company's employees in that department and want to know
Without employees, we can use the above query statement to be implemented.
Left outer join and Right Outer Join are as follows above, the results of the query are the same.
Left outer connection: Select Empno, Ename, Sal, Emp.deptno, Dept.deptno from Dept Left Outer Join Emp On (Emp.Deptno = Dept.deptno)
Right connection: Select Empno, Ename, Sal, Emp.Deptno, Dept.deptno from Emp REight Outer Join Dept ON (Emp.deptno = Dept.deptno)
The result of the above display and the result of the external connection query is the same, just the way it is not the same, the actual work is more connected, there is no need to use it, but the concept must know.
All external relationship: 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);
The result is: All employees and corresponding sectors, including employee records that do not correspond to department numbers department_id, and department records without any employees.
