Difference between inner and outer connections in SQL Server

Difference between inner and outer connections in SQL Server

Assume that a database has two tables, one is studentinfo, and the other is classinfo. The two tables are associated with each other using the classid field. If you use an internal connection, the normal method is as follows: Select studentinfo. *, classinfo. * From studentinfo join classinfo on studentinfo. classid = classinfo. classid The Cartesian product is not used in this method, but the inner join can omit the join condition, that is, the content after on can be omitted. Therefore, if it is written as follows: Select studentinfo. *, classinfo. * From studentinfo, classinfo It can also be compiled, but in this way a Cartesian product is generated (for the content of the Cartesian Product, see the final annotation of the article ). However, it is incorrect to refuse to use the inner link because it is afraid of generating cartesian products. As long as the join conditions are not omitted, cartesian products will not be generated. The specific use of internal connections or external connections depends on the needs of specific problems. The internal join feature only displays records that meet the connection conditions. Taking the preceding statement as an example, the query results only show records with the same classid as the student table and the class table. For example, if the classid field of a student record is null, the student record is not displayed in the internal link, because the corresponding classid cannot be found in the class table. The outer connection is different. The outer left connection is used as an example (the outer right connection is similar). In addition to displaying records that meet the connection conditions, all records in the left table are displayed (the right outer join is all records in the right table ). The preceding example shows that if the classid field of a student record in the student table is null and the class table is connected to the left outer corner of the student table, this student record is displayed. Therefore, if you want to display only the records of students in different classes, you should use an internal connection for query. If you want to query all student records, you should use an external connection for query.   Cartesian product: Assume that the Set A = {a, B}, and set B = {0, 1}, the Cartesian product of the Two sets is {(A, 0), (A, 1 ), (A, 2), (B, 0), (B, 1), (B, 2 )}. It can be expanded to multiple sets. In a similar example, if a represents a set of students in a school and B represents a set of all courses in the school, cartesian products of A and B represent all possible course selections.