Connection query improves select efficiency instead of nested queries

When a database contains a large amount of data, and the information we need is no longer limited to the data in a single table, we need to query the table for information and then combine it into multiple tables.

To solve such problems we usually use nested Query method, first executes the internal subquery and then the subquery results as the outer query data source, and finally on the basis of the main query. This would be equivalent to a two-time query SELECT statement with less efficiency. After the introduction of the connection, SQL Server will perform just one query.

If there is a student table and curriculum, the choice of three classes, the inquiry took the names of the students of chemistry.

T_student

student_id	Student_name	Student_age
1	Li ming	11
2	Tom	12

T_course

course_id	Course_name
1001	Chemical
1002	Biological

T_selecet

student_id	course_id	Grade
1	1001	80
2	1002	85

Nested queries:

Select Student_name from t_student where t_student.student_id into
(select t_select.student_id from T_select where T_se lect.course_id in
(select t_course.course_id from T_course where course_name= ' chemistry ')  )
go

Connection Query

Select T_student.student_name from t_student join T_select on
t_student.student_id=t_select.student_id join T_ Course on
t_course.course_id=t_select.course_id
where t_course.course_name= ' chemistry '
go

The results of the query in both of these ways are:

The above two types of queries show that a nested query is equivalent to executing 3 queries and the connection query is only executed once.

The result of the connection query is a query result composed of many tables, so the result of the query has a great relationship with the way of connection, so the connection can be divided into inner connection, left outer join, right outward connection, complete outer connection, cross connection several connection ways. several ways of joining can completely replace the results that a nested query might query, as in the previous example, so if you want to increase the query efficiency of a SELECT statement on a query, you can replace the nested query with a connection query.

The inner Connection is table 1 inner JOIN table 2on its result returns all matching rows in two tables

left-outward join table 1 leftouter join table 2on Returns the result set will include all the records in Datasheet 1, not just the records that the connection fields match. If a record in Datasheet 1 does not have a matching record in Datasheet 2, all fields of data table 2 for the corresponding record in the result set are null values.

right outer JOIN Table 1 outer join (Rightjoin) Table 2 on Returns the result set will include all the records in Datasheet 2, not just the records that the joined Fields match. If a record in Datasheet 2 does not have a matching record in Datasheet 1, then all fields related to datasheet 1 for the corresponding record in the result set will be null

complete outer JOIN table 1 full outer JOIN table 2on result set will contain all the records in two data tables, and specify a blank value for another datasheet when there is no matching record in another data table

cross-connect   Table 1 Cross Join table 2 on if the WHERE clause is not used in the SELECT statement, the cross join returns the number of flutes Descartes the records in Datasheet 1 and Datasheet 2, that is, the cross join returns all the records in Datasheet 1, And a combination of each record in Datasheet 1 and all the records in Datasheet 2.