Differences between intra-and outer-connection in SQL Server

Differences between intra-and outer-connection in SQL Server

Assume that there are two tables in a database, one is the student table Studentinfo, one is the class table ClassInfo, and the two tables are associated with CLASSID fields. If you are using an inner join, the normal wording is this: Select studentinfo.*,classinfo.* from Studentinfo join ClassInfo on Studentinfo.classid=classinfo.classid The Cartesian product does not appear in this notation, but the inner join is allowed to omit the join condition, that is, to omit the content on the back, so if it is written like this: Select studentinfo.*,classinfo.* from Studentinfo,classinfo can also be compiled, but this results in a Cartesian product (see the final note of the article for a Cartesian product). However, because it is not correct to refuse to use the inner link because of the fear of producing Cartesian product, the Cartesian product will not be produced as long as the connection condition is not omitted. Whether to use internal or external connections depends on the specific problem. The inner join feature is to display only records that meet the join criteria, as in the previous statement, the query results show only records that are classid equal in the Student and class tables. For example, if the ClassID field for a student record is empty, the link will not show the student record because the corresponding ClassID is not found in the class table. The Connection is not the same, with a left outer join as an example (a right outer join is similar), in addition to displaying records that meet the join criteria, it also displays all records in the left table (the right outer join is the record in all the right tables). Or just to illustrate, if the student table has a student record of the ClassID field is empty, with the student table left outside the class table, then the student record will be displayed. So if you want to show only student records that have already been split, you should use an internal connection query, and if you want to query all student records, you should use an outer join query.   Cartesian product: assumes set a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases. Similarly, if a represents a collection of students in a school and b represents a collection of all courses in the school, the Cartesian product of A and B represents all possible elective courses.

Differences between intra-and outer-connection in SQL Server