JAVA outer connection

Outer joins are divided into left outer join, right outer join and full outer join. The inner join we learned earlier obtains records that meet the conditions, and the records that do not meet the conditions in the join query table are removed. The characteristic of outer joins is that the query results may not meet the conditions.
1. Left outer join
Left outer join queries all the records in the left table. The values ​​of the columns in the right table corresponding to the records in the left table that do not meet the conditions are displayed as NULL. The keyword of left outer join is: LEFT OUTER JOIN, and the corresponding sql statement is as follows:
SELECT * FROM table 1 alias 1 LEFT OUTER JOIN table 2 alias 2 ON alias 1. column name = alias 2. column name
where table 1 is The left table is also called the main table, and Table 2 is the right table.
Use left outer join to query the information of each employee and department information. The corresponding query statement is: select * from emp e left outer join dept d on e.deptno=d.deptno,
It can be seen from the query results that when the left table When the records do not meet the conditions, the value of the column in the right table is null.
2. Right outer join
Right outer join queries all the records in the right table. The values ​​of the columns in the left table corresponding to the records in the right table that do not meet the conditions are displayed as NULL. The keyword for right join is: RIGHT OUTER JOIN, and the corresponding sql statement is as follows:
SELECT * FROM table 1 alias 1 RIGHT OUTER JOIN table 2 alias 2 ON alias 1. column name = alias 2. column name
where table 1 is the left Table, Table 2 is the right table, also called the main table.
Use right outer join to query the information of each department and the department’s employee information. The corresponding query statement is: select * from emp e right outer join dept d on e.deptno=d.deptno,
3. Full outer join
Full outer join has no restrictions on the left table and the right table. All records are displayed, and the places in the two tables that do not meet the conditions are filled with null. The keyword of full outer join is: FULL OUTRE JOIN, and the corresponding sql statement is as follows:
SELECT * FROM Table 1 Alias ​​1 FULL OUTER JOIN Table 2 Alias ​​2 ON Alias ​​1. Column name = Alias ​​2. Column name
MySQL database does not support Full outer join, but from an analysis point of view, full outer join combines the results of left outer join and right outer join.
Use full outer join to query the information of emp table and dept table
SELECT
*
FROM
emp e LEFT OUTER JOIN dept d
ON
e.deptno=d.deptno
UNION
SELECT
*
FROM
emp e RIGHT OUTER JOIN dept d
ON
e.deptno=d.deptno
In the query results in Figure 1-3, the corresponding positions in the two tables that do not meet the conditions are replaced by null.
Use left outer join query. The left table is e1, so all employee names in the e1 table are queried, and the positions in the e2 table that do not meet the conditions are replaced with null.