Outer joins (OUTER join)

9.3.4 Outer joins (OUTER join)

Multiple table queries, whether inline or with a WHERE clause, are grouped together from multiple tables and generate a result table. In other words, if a row in any one of the source tables does not match in another source table, the DBMS will not place the row in the final result table.

An outer connection tells ODBC to generate a result table that contains not only rows that meet the conditions of the join, but also all rows of data in the left table (when the left outer join), the right table (when the right outer join), or the two side tables (all outer joins).

There are 3 types of external connections to the SQL: Left outer JOIN, OUTER join, right outer join, OUTER join and all outer joins, keyword full OUTER join. The use of an outer join is the same as an inner join, except that the INNER JOIN keyword is replaced with the appropriate outer join keyword.

Explains that keyword OUTER is optional when using an outer join, such as a free left join instead of the left OUTER join.

Here are some of these outer joins.

1. Left OUTER JOIN

Left OUTER join, which tells the DBMS that the resulting table, in addition to the matching rows, includes a mismatched row from the left table in the JOIN keyword (in the FROM clause). The left outer join can actually be expressed as:

Left OUTER JOIN = internal join + mismatch tuple in left table

Where the property values in the missing table to the right are represented by NULL. Figure 9.17 shows a typical left-outer connection schematic.

Figure 9.17 LEFT OUTER JOIN

Instance 11 left-connected Student table and course table

Left Join student table and course table, query all students ' number, name, course code, course name, test time and performance information. Instance code:

SELECT S.sno, Sname, S.cno, CNAME, CTest, MARK From STUDENT as S Left OUTER JOIN COURSE as C On S. CNO = C. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.18.

Visible, the resulting resulting table, in addition to the two table matching row (3~20 row), also includes the left table student in the mismatched rows (1, 2 rows), missing the right table, that is, course table property values are represented by NULL.

Tip in SQL Server, you can use the "*=" symbol in the WHERE clause to implement a left outer join.

Figure 9.18 Query results for Student table and course table with left connection

In the WHERE clause, use the "*=" symbol to implement the example of the left outer join implementation, as follows.

SELECT S.sno, Sname, S.cno, CNAME, CTest, MARK From STUDENT as S, COURSE as C WHERE S. CNO *= C. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.19.

Figure 9.19 left outer joins implemented using the "*=" symbol

Description in Oracle database system, just replace "*=" with "+ +" to get the same result.

2. Right outer join

The right OUTER join tells the DBMS that the resulting table, in addition to the matching rows, includes a mismatched row from the right table in the JOIN keyword (in the FROM clause). The right outer join can actually be expressed as:

Right outer join = INNER JOIN + mismatch tuple in right table

Where the value of the property in the left table missing is represented by NULL. Figure 9.20 shows a typical right outer join schematic.

Figure 9.20 Right outer join

Example 12 right outer join Student table and course table

Right outer joins Student table and course table, inquires all schoolmate's student number, the name, the course code, the course name, the examination time and the result information. Instance code:

SELECT S.sno, Sname, S.cno, CNAME, CTest, MARK From STUDENT as S Right OUTER JOIN COURSE as C On S. CNO = C. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.21.

Figure 9.21 Query results for student tables and course tables with right outer joins

Visible, the resulting resulting table, in addition to the two table matching row (3~20 row), also includes the Right Table course table in the mismatched rows (1, 2 rows), missing table left, that is, Student table property values are represented by NULL.

Tip in a SQL Server database system, you can use the "=*" symbol in the WHERE clause to implement a right outer join.

Instance 13 using the "=*" symbol in the WHERE clause to implement the right outer join

In the WHERE clause, use the "=*" symbol to implement instance 12 with the following code.

SELECT S.sno, Sname, S.cno, CNAME, CTest, MARK From STUDENT as S, COURSE as C WHERE S. CNO =* C. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.22.

Figure 9.22 the right outer join implemented using the "=*" symbol

3. Full outer connection

All outer joins, full OUTER join, tells the DBMS that the resulting table, in addition to the matching rows, includes a mismatched row from the left and right tables in the JOIN keyword (in the FROM clause). A full outer join can actually be expressed as:

All outer joins = The missing tuples in the left table and the mismatching tuples in the right table.

Where the missing table on the left or the property value in the right table is represented by NULL. Figure 9.23 shows a typical full outer join schematic.

Figure 9.23 Full outer joins

Example 14 full outer join Student table and course table

All outer joins Student table and course table, inquires all schoolmate's student number, the name, the course code, the course name, the examination time and the result information. Instance code:

SELECT S.sno, Sname, S.cno, CNAME, CTest, MARK From STUDENT as S Full OUTER JOIN COURSE as C On S. CNO = C. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.24.

Figure 9.24 Query results for student tables and course tables with all outer joins

Visible, the resulting resulting table, in addition to the two table matching row (5~22 row), also includes the Right Table course table in the mismatched rows (1, 2 rows), missing table left, that is, Student table property values are represented by NULL. And the table on the left, student rows in the table (3, 4 rows), the missing table on the right, that is, the value of the property in the course table is represented by NULL.