T-SQL LEFT JOIN do you really understand?

In the previous T-SQL join keyword, I made a simple analysis of the JOIN keyword. Then in the actual application according to the demand, the join between the multiple tables is unavoidable. Here's a summary of what I've experienced in the project about the use of the Join keyword once again, for future review and review.

Let's look at the relationship between the database tables used for this summary, as follows:

Now start our needs, to find out such student:student major (professional can be for multiple) at least one or it itself does not have the professional requirements and the specified school contains a major (professional can also be multiple and at least one ) is matched (the score score is not considered here for the time being).

Look: This is not simple, so directly wrote the following T-SQL statement (below is the query result):

SELECT *  fromStudent SJOINStudentmajor SM onS.studentid=SM. StudentID Left JOIN (   SELECTS.*Sm. Majorid fromSchool SJOINSchoolmajor SM onS.schoolid=Sm. SchoolIDWHERES.schoolid=1)  asScm_temp onSm. Majorid=Scm_temp. Majorid

View Code

The following T-SQL is written according to this without hesitation:

SELECT *  fromStudent SJOINStudentmajor SM onS.studentid=SM. StudentID Left JOIN (   SELECTS.*Sm. Majorid fromSchool SJOINSchoolmajor SM onS.schoolid=Sm. SchoolIDWHERES.schoolid=1)  asScm_temp onSm. Majorid=scm_temp. MajoridWHERESm. Majorid is NULL ORScm_temp. Majorid is  not NULL 

View Code

The test data query results are as follows:

Not just student union Studentmajor query out as a set and then make a LEFT JOIN leftist table query with school and schoolmajor data collection?

As a result, I overlooked the highlight of the above requirement. This is not the case of the student table in the absence of any professional requirements to be screened out. Look at the following code and the result of the execution:

SELECT *  fromStudent S Left JOINStudentmajor SM onS.studentid=SM. StudentID Left JOIN (   SELECTS.*Sm. Majorid fromSchool SJOINSchoolmajor SM onS.schoolid=Sm. SchoolIDWHERES.schoolid=1)  asScm_temp onSm. Majorid=Scm_temp. Majorid

View Code

According to this analysis, there is the final result.

SELECT *  fromStudent S Left JOINStudentmajor SM onS.studentid=SM. StudentID Left JOIN (   SELECTS.*Sm. Majorid fromSchool SJOINSchoolmajor SM onS.schoolid=Sm. SchoolIDWHERES.schoolid=1)  asScm_temp onSm. Majorid=scm_temp. MajoridWHERESm. Majorid is NULL ORScm_temp. Majorid is  not NULL 

View Code

That's right. The table and data for the sample description are added temporarily, and do not involve actual development of the real data being designed. Just to illustrate the problem. Of course, this is only because I have a shallow knowledge of the reasons, the example is not very appropriate. Prawn See, please correct the improper place, help me to learn progress, thank you!