Use join queries instead of nested queries to improve select Efficiency

When a database contains a large amount of data, the information we need is no longer limited to the data information in a table, in this case, we need to query the information of a table to search and combine the information of multiple tables.

To solve this problem, we usually use the nested query method. First, execute the internal subquery and then use the subquery result as the data source of the outer query, finally, perform the primary query based on the primary query. In this way, the efficiency of the SELECT statement for two queries is also reduced. After the connection is introduced, SQL Server will only execute a query.

Suppose there are three tables: Student table, Course table, and course selection. query the names of students who have selected chemistry.

T_student

Student_id	Student_name	Student_age
1	Li Ming	11
2	Zhang San	12

T_course

Course_id	Course_name
1001	Chemistry
1002	Creature

T_selecet

Student_id	Course_id	Grade
1	1001	80
2	1002	85

Nested query:

Select student_name from t_student where t_student.student_id in (select t_select.student_id from t_select where t_select.course_id in (select t_course.course_id from t_course where course_name = 'Chemical') Go

Connection Query

Select t_student.student_name from t_student join t_selecton t_student.student_id = t_select.student_id join t_courseon t_course.course_id = t_select.course_idwhere t_course.course_name = 'Chemical' go

The query results are as follows:

From the preceding two query methods, we can see that nested query is equivalent to executing three queries, while connection query is only executed once.

The connection query result is composed of multiple tables. Therefore, the query result is closely related to the connection method, therefore, connections can be divided into internal connections, left outer connections, right outer connections, complete outer connections, and cross connections.Several connection methods can replace the results that may be queried by nested queries, just as in the previous example, therefore, to improve the query efficiency of select statements in queries, you can use join queries instead of nested queries.

Inner join: Table 1 inner join (join) Table 2on. The result returns all matched rows in the two tables.

Left Outer Join Table 1 leftouter join (left join) Table 2on the returned result set will include all the records in Table 1, not just the records matching the join fields. If a record in Table 1 does not have a matched record in Table 2, all fields in Table 2 related to the corresponding record in the result set are null.

Right Outer Join Table 1 right Outer Join (rightjoin) Table 2 on return result set will include all the records in Table 2, not just the records matching the joined fields. If a record in Table 2 does not match in Table 1, all fields related to table 1 in the corresponding record in the result set are null.

Full outer join Table 1 full outer join (full join) Table 2on result set will contain all records in two data tables. When one record does not match in another data table, specify the selection list field of another data table as a null value.

Cross join Table 1 cross join table 2 on if the WHERE clause is not used in the SELECT statement, the cross join returns the flute product recorded in table 1 and table 2, that is to say, the result returns all records in table 1 and the combination of each record in Table 1 and all records in table 2.