Left and right connections of SQL, and SQL connections

Left and right connections of SQL, and SQL connections
External Connection
When an external connection is used for multi-table queries, the returned query results only contain rows with query conditions and connection conditions. Except for eliminating any unmatched rows in the other table, the inner join extends the result set of the inner join, except for returning all matched rows, some or all of the unmatched rows are returned, depending on the type of the outer join.
For external connections, you can use (+) in Oracle or the left right and full outer join keywords.
External connections can be divided into the following three types:
Left outer join: (left outer join or left join)
Outer right join: (right outer join or right join)
Full outer join: (full outer join or full join)
Use external join to list the rows that match the connection conditions, and list the rows in the left table (left Outer Join), right table (right table join), or two tables (all Outer Join, all data rows that meet the search criteria.
Example:
We use the emp and salgrade tables that come with oracle for operations.
The salgrade table indicates the wage level, with the highest and lowest values for each wage level. Emp table is the basic information of employees.
SQL> select * from salgrade;
GRADE LOSAL HISAL
------------------------------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select empno, ename, sal, grade
2 from emp e inner join salgrade s
3 on e. sal between s. losal and s. hisal;


EMPNO ENAME SAL GRADE
----------------------------------------
7369 SMITH 800 1
7876 ADAMS 1100 1
7900 JAMES 950 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7902 FORD 3000 4
7839 KING 5000 5
14 rows have been selected.
SQL> insert into emp values (7937, 'candy ', null, 500, null, null ); // The sal salary in the inserted data is 500 and is not within the employee's salary range.
One row has been created.
SQL> insert into salgrade values (6, 20000,); -- the insertion level and salary are no longer within the scope.
One row has been created.
SQL> select e. empno, e. ename, e. sal, d. grade
2 from emp e left outer join salgrade d -- use left join
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
15 rows have been selected.
From the above data, we can see that in addition to finding all qualified data, we have also investigated and dealt with data out of scope.
7937 Candy 500








Outer right connection
The outer right join shows all rows that meet the search conditions in the table on the right of join in addition to the rows that meet the search conditions in the results.
Select distinct e. deptno, d. deptno from emp e right outer join dept d on e. deptno = d. deptno;
If you use jiahao to implement the right outer join, the preceding statement is equivalent to the following statement:
Select distinct e. deptno, d. deptno from emp e, dept d where e. deptno (+) = d. deptno;
The result is as follows:
SQL> select distinct e. deptno, d. deptno from emp e, dept d where e. deptno (+) = d. deptno;


DEPTNO
--------------------
30 30
20 20
10 10
40


SQL> select distinct e. deptno, d. deptno from emp e right outer join dept d on e. deptno = d. deptno;


DEPTNO
--------------------
30 30
20 20
10 10
40


SQL> select e. empno, e. ename, e. sal, d. grade
2 from emp e right outer join salgrade d
3 on e. sal between d. losal and d. hisal;
EMPNO ENAME SAL GRADE
----------------------------------------
7369 SMITH 800 1
7876 ADAMS 1100 1
7900 JAMES 950 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7902 FORD 3000 4
7839 KING 5000 5
6
// If the wage level is 6, no data is displayed, but the right join is used to display unmatched data in the salgrade table.
15 rows have been selected.
SQL> select distinct e. deptno, d. deptno
2 from emp e right outer join dept d
3 on e. deptno = d. deptno;
DEPTNO
--------------------
30 30
20 20
10 10
40
SQL> select distinct e. deptno, d. deptno
2 from emp e, dept d
3 where e. deptno (+) = d. deptno; // same as the preceding result
DEPTNO
--------------------
30 30
20 20
10 10
40