A full outer join is a result in which all rows in the table of a join that satisfy the search criteria are displayed in addition to the rows that meet the conditions of the connection.
Sql> Select E.empno,e.ename,e.sal,d.grade
2 from EMP e full outer join Salgrade D
3 on e.sal between D.losal and D.hisal;
EMPNO ename SAL GRADE
---------- ---------- ---------- ----------
7839 KING 5000 5
7902 FORD 3000 4
7788 SCOTT 3000 4
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7499 ALLEN 1600 3
7844 TURNER 1500 3
7934 MILLER 1300 2
7521 WARD 1250 2
7654 MARTIN 1250 2
7876 ADAMS 1100 1
7900 JAMES 950 1
7369 SMITH 800 1
7937 Candy 500 6
In the results above
7937 Candy 500
This piece of data does not have a corresponding wage level,
6
This data does not have the corresponding employee information, the right outer connection
Cross Connect
With cross Joi, you can cross-connect two tables, and the result is a combination of all the rows of data in both tables, the Cartesian product of all the data rows of the two tables.
Cross-joins are very similar to simple join operations, but when using cross joins, the multiple table names in the FROM clause are not comma-separated from each other by using the crosses join keyword. Outside of the cross join, you do not need to use the keyword on to qualify the join condition, but you can add a WHERE clause to set the join condition.
Sql> Select Empno,ename,e.deptno,d.deptno
2 from EMP e cross join Dept D;
EMPNO ename DEPTNO DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 20 10
7499 ALLEN 30 10
7521 WARD 30 10
7566 JONES 20 10
7654 MARTIN 30 10
7698 BLAKE 30 10
7782 CLARK 10 10
7788 SCOTT 20 10
7839 KING 10 10
7844 TURNER 30 10
7876 ADAMS 20 10
7900 JAMES 30 10
7902 FORD 20 10
7934 MILLER 10 10
7937 Candy 10
7369 SMITH 20 20
7499 ALLEN 30 20
7521 WARD 30 20
7566 JONES 20 20
7654 MARTIN 30 20
7698 BLAKE 30 20
7782 CLARK 10 20
7788 SCOTT 20 20
7839 KING 10 20
7844 TURNER 30 20
7876 ADAMS 20 20
7900 JAMES 30 20
7902 FORD 20 20
7934 MILLER 10 20
7937 Candy 20
7369 SMITH 20 30
7499 ALLEN 30 30
7521 WARD 30 30
7566 JONES 20 30
7654 MARTIN 30 30
7698 BLAKE 30 30
7782 CLARK 10 30
7788 SCOTT 20 30
7839 KING 10 30
7844 TURNER 30 30
7876 ADAMS 20 30
7900 JAMES 30 30
7902 FORD 20 30
7934 MILLER 10 30
7937 Candy 30
7369 SMITH 20 40
7499 ALLEN 30 40
7521 WARD 30 40
7566 JONES 20 40
7654 MARTIN 30 40
7698 BLAKE 30 40
7782 CLARK 10 40
7788 SCOTT 20 40
7839 KING 10 40
7844 TURNER 30 40
7876 ADAMS 20 40
7900 JAMES 30 40
7902 FORD 20 40
7934 MILLER 10 40
7937 Candy 40
60 rows have been selected.
Cross Connect if you do not use any of the conditional restrictions, you will get the Cartesian product of the data, which is the combination of the data in each table with the data in the other table.
Sql> Select Empno,ename,e.deptno,d.deptno
2 from EMP e cross Join Dept D
3 where d.deptno=10
4 and e.deptno=10;
EMPNO ename DEPTNO DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 10 10
7839 KING 10 10
7934 MILLER 10 10
Limit the Deptno in two tables
Cross-connect and full-connect
