The relationship between the employee and the boss in the EMP table
Internal connections:
The inner join is also called a natural connection, which is a common method of combining two tables. A natural connection compares columns in two tables, combining rows in two tables that meet the conditions of the join, as a result. A natural connection has two forms of syntax.
Select E1.ename,e1.mgr,e2.ename AA from EMP e1,emp E2 where e1.mgr=e2.empno;
Select E1.ename,e1.mgr,e2.ename AA from EMP E1 INNER JOIN
EMP E2 on
E1.mgr=e2.empno;
The result is: all have the superior employee and corresponding superior name.
Outer joins:
In a natural connection, only rows that match in two tables can appear in the result set. In an outer join, you can limit only one table, and the other table without restrictions (that is, all rows appear in the result set).
Outer joins are divided into left outer joins, right outer joins and all outer joins. The left outer join is unrestricted to the table on the left of the join condition, the right outer join is unrestricted to the table on the right, and the full outer join is unrestricted for all two tables, and the rows in all two tables are included in the result set.
The syntax for the left OUTER join is:
SELECT column from table 1 left [Outer]join table 2 on table 1 1= table 2. Column 2
The syntax for the right outer join is:
SELECT select_list from table 1 right[outer]join table 2 on table 1 1= table 2. Column 2
The syntax for an all outer join (full outer join) is:
SELECT select_list from table 1 Full[outer] JOIN table 2 on table 1. Column 1= table 2. Column 2
Left OUTER join:
Left outer joins are unrestricted to the table on the left of the join condition
Select E1.ename,e1.mgr,e2.ename AA from EMP e1,emp E2 where E1.MGR=E2.EMPNO (+)
;
Select E1.ename,e1.mgr,e2.ename AA from EMP E1 left
Outer JOIN
EMP E2 on
E1.mgr=e2.empno;
The result is: all employees and corresponding superior names (including employee records without superiors).
Right outer connection:
The right outer join is unrestricted for the table on the right
Select E1.ename,e1.mgr,e2.ename AA from EMP e1,emp E2 where e1.mgr (+)
=e2.empno;
Select E1.ename,e1.mgr,e2.ename AA from EMP E1 right outer JOIN
EMP E2 on
E1.mgr=e2.empno;
The result is: all employees and corresponding superior names, plus no subordinate staff's superior record.
Full Outer connection:
All outer joins are unrestricted for two tables, and all two of the rows in the table are included in the result set
*/
Select E1.ename,e1.mgr,e2.ename AA from EMP E1 full outer JOIN
EMP E2 on
E1.mgr=e2.empno;
The result is: all employees and corresponding superior names (including the employee records without superiors), plus a supervisor record with no subordinate employees.
Cross join:
A cross join without a WHERE clause produces a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set.
Select E1.ename,e1.mgr,e2.ename AA from EMP E1 cross Join
EMP E2;
Select E1.ename,e1.mgr,e2.ename AA from EMP E1, EMP E2;
The result is: a Cartesian set of all the employees in the two tables.
/* LEFT OUTER JOIN and right outer join two ways to return the same result, but the arrangement of different ways * *