Joint database query-Tips

No matter whether we are learning the instructor's video or the principle of the self-testing database, we are all connected to the joint query part, but we have not applied it too much in practice. Now, only by doing projects can we truly realize the importance of applying theory to practice. 1. Conceptual joint query is to retrieve data from two or more tables based on the logical relationship between each table.

No matter whether we are learning the instructor's video or the principle of the self-testing database, we are all connected to the joint query part, but we have not applied it too much in practice. Now, only by doing projects can we truly realize the importance of applying theory to practice. 1. Conceptual joint query is to retrieve data from two or more tables based on the logical relationship between each table.

No matter whether we are learning the instructor's video or the principle of the self-testing database, we are all connected to the joint query part, but we have not applied it too much in practice. Now, only by doing projects can we truly realize the importance of applying theory to practice.

I. Concepts

A joint query is used to retrieve data from two or more tables based on the logical relationship between each table. This logical relationship is the association of the columns of each table, this is also the most important feature of relational database queries.

Data Table connections include:

1. Internal Connection

2. External Connection

(1) left join (no restrictions on the left table)

(2) Right join (no restrictions on the right table)

(3) All external connections (unrestricted)

3. Cross join

Ii. Practice

Create two tables, one student management table (T_ManageStudent) and the other student information table (T_StudentInfo)

Table 1: (Student Management table ):

Table 2: (student information table)

1. Internal Connection

Compare the two tables and combine them to meet the connection conditions as a result.

Statement:

Side 1: select dbo. t_ManageStudent. as number 1, dbo. t_ManageStudent. name, dbo. t_StudentInfo. number as 2, dbo. t_StudentInfo. title from T_ManageStudent inner join T_StudentInfo on T_ManageStudent. no. = T_StudentInfo. no.

Side 2: select. as number 1,. name, B. as number 2, B. title from T_ManageStudent as a inner join T_StudentInfo as B on. no. = B. no.

Result:

2. External Connection

(1) left join (no restrictions on the left table)

The returned result set contains all records in T_ManageStudent, not just records that match the connection fields. If a record in T_ManageStudent does not match a record in T_StudentInfo, The T_StudentInfo part of the corresponding record in the result set is NULL.

Statement:

Side 1: select dbo. t_ManageStudent. as number 1, dbo. t_ManageStudent. name, dbo. t_StudentInfo. number as 2, dbo. t_StudentInfo. title from T_ManageStudent left join T_StudentInfo on T_ManageStudent. no. = T_StudentInfo. 2: select. as number 1,. name, B. as number 2, B. title from T_ManageStudent as a left join T_StudentInfo as B on. no. = B. no.

Result:

(2) Right join (no restrictions on the right table)

The returned result set contains all records in T_StudentInfo, not just records that match the connection fields. If a record in T_StudentInfot does not match a record in T_ManageStudent, The T_ManageStudent part of the corresponding record in the result set is NULL.

Statement:

Side 1: select dbo. t_ManageStudent. as number 1, dbo. t_ManageStudent. name, dbo. t_StudentInfo. number as 2, dbo. t_StudentInfo. title from T_ManageStudent right join T_StudentInfo on T_ManageStudent. no. = T_StudentInfo. 2: select. as number 1,. name, B. as number 2, B. title from T_ManageStudent as a right join T_StudentInfo as B on. no. = B. no.

Result:

(3) All external connections (unrestricted)

The returned result set contains all records that match and do not match T_ManageStudent and T_StudentInfo.

Statement:

Side 1: select dbo. t_ManageStudent. as number 1, dbo. t_ManageStudent. name, dbo. t_StudentInfo. number as 2, dbo. t_StudentInfo. title from T_ManageStudent full join T_StudentInfo on T_ManageStudent. no. = T_StudentInfo. no.

Side 2: select. as number 1,. name, B. as number 2, B. title from T_ManageStudent as a full join T_StudentInfo as B on. no. = B. no.

Result:

3. Cross join

Case 1 (no where ):

The result set of the first table is multiplied by the row of the second table.

Case 2 (with where ):

Same as internal connection

Statement:

Select T_ManageStudent. No. as 1, T_ManageStudent. Name, T_StudentInfo. No. as 2 from T_ManageStudent cross join T_StudentInfo

Result: