INNER JOIN (INNER join)

9.3.3 INNER JOIN (INNER join)

The inner join is also called an equivalent connection, and the result set returned is all the matching data in the two tables, and the mismatched data is discarded. That is, in such a query, the DBMS returns only the related rows from the source table, that is, the two source table rows contained in the query's result table must satisfy the search criteria in the ON clause. In contrast, if the row in the source table does not have a corresponding (related) row in the other table, the row is filtered out and not included in the result table. The inner joins are done using comparison operators. The inner joins can be divided into two types.

1. Equivalent connection

Use the equals sign (=) to compare the column values of the connected columns, listing all the columns in the linked table, including the repeating columns, in the query results. Fig. 9.12 shows a typical inner-contour connection diagram.

Figure 9.12 Equivalent INNER JOIN

Instance 8 using INNER JOIN query

Check the names of the students, their departments, the course numbers of all the courses and the names of the student from the table and the teacher table. In section 9.3.2, this instance has been implemented through a WHERE clause, which is implemented by joining two tables in the inner join. Instance code:

SELECT sname, S.dname, S.cno, Tname From STUDENT as S INNER JOIN TEACHER as T On S. CNO = T. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.13.

Figure 9.13 Query results implemented using INNER JOIN

As you can see, the same result is achieved by joining the two tables through the inner join.

2. Unequal connection

In a join condition, you can use other comparison operators to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<, and <>.

Instance 9 queries with unequal connections

To inquire about the course information offered by students who are not teachers of the department, including student number, name, department, course number, grade, and the name information of the class teacher. Instance code:

SELECT S.sno, Sname, S. Dname, S.cno, MARK, Tname From STUDENT as S INNER JOIN TEACHER as T On S. dname<> T. dname and S. CNO =t. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.14.

Figure 9.14 Query results with unequal connections

Describes that by default, a DBMS executes a multiple-table query by pressing INNER JOIN unless a OUTER join is specified. That is, if you replace the INNER JOIN keyword in the previous example with a join, you get the same result.

Of course, you can also use the WHERE clause to implement the above example. The code is as follows.

SELECT S.sno, Sname, S. Dname, S.cno, MARK, Tname From STUDENT as S, TEACHER as T WHERE s.dname <> T.dname ORDER BY Sname

The results of the operation are shown in Figure 9.15.

Of course, using the inner join can also implement a multiple-table inner join, but inner join can only connect two tables at a time, and multiple tables must be connected.

Figure 9.15 Unequal connections implemented using a WHERE clause

Instance 10 uses inner join to implement a multiple-table inner JOIN

According to the teacher, course and student tables, all students are queried for their names, their departments, the courses they have taken, the time of their exams, the results of their courses and the names of the lecturers. This column is identical to the example in Section 9.2.3, but is implemented in the case of the WHERE clause in the 9.2.3 section, which is implemented in the form of inner joins, as follows.

SELECT sname, STUDENT. Dname, CNAME, CTest, MARK, Tname From TEACHER On TEACHER. CNO = COURSE. CNO INNER JOIN STUDENT On TEACHER. CNO = STUDENT. CNO ORDER BY Sname

The results of the operation are shown in Figure 9.16.

Figure 9.16 using inner join to implement inner join query results