One 、 Preface

Recently at work , Encountered such a scene ：
Yes 2 Database tables ,
One of them is the class schedule class_course, It records how many courses there are in a class , The fields are as follows ：

Pictured ,a Ben you 4 Course ,b Ben you 2 Course ;

The other is the student class record form student_course, It records which courses a student has taken , The fields are as follows ：

Pictured ,zhangsan01 Yes a Class 3 Course ,b Class 1 Course ;
lisi Yes b Class 2 Course ;

problem ： How to query ,zhangsan01 Is it finished a All the courses in class ？

Two 、 Solutions

First , I thought , According to course_id, use class_course The left couplet of the table student_course, Then filter out a Class 、zhangsan01 The record of ;
If there is an associated query student_course.course_id by null Of , Just explain zhangsan01 I haven't taken this course , Just explain zhangsan01 Not finished a All the courses in class ;
If it doesn't exist null, Just explain zhangsan01 It's over a All the courses in class .
Pictured ：

As long as you can use sql Find such List<String>, then java Inside circulation , Find promising null Of , explain zhangsan01 I really didn't go a Class a4 course , You can also get zhangsan01 Not finished a Conclusion of all courses in class .

In this way, the requirements can be realized , The next question is sql How to write .

3、 ... and 、 Step on the pit process —— error sql

1. The conditions are all written in on Inside

select b.course_id,a.* from class_course a left join student_course b 
on a.course_id=b.course_id and a.class_id='a' and b.student_id='zhangsan01';

What I first thought of sql this is it , But the results of the query do not meet the requirements ：

although on There are limited conditions a.class_id=‘a’, But I don't know why the result still exists b Class data , This query is still wrong .

2. Main conditions where Inside

select b.course_id,a.* from class_course a left join student_course b 
on a.course_id=b.course_id 
where a.class_id='a' and b.student_id='zhangsan01';

First of all, we should on a.course_id=b.course_id , There's no doubt about it ;
Then write because of the condition on Inside wrong , So I try to write in where in , But the result is still wrong ：

Because in the sample data ,zhangsan01 Not on a4 course , So there should be a line null Of ;
But this did not find null, It's not right either .

Four 、 correct sql

Part of the condition is on in , Part of the condition is where in ：

select b.course_id,a.* from class_course a left join student_course b 
on a.course_id=b.course_id and a.class_id='a' and b.student_id='zhangsan01'
where a.class_id='a';

The result is shown in Fig. ：

sql in ,on It limits the use of a Class 、zhangsan01 Perform an associated query on the data of ;

But if only on If there are restrictions in , Because it is the left wing League , therefore class_course Other data in the table will also be displayed , It's just connected student_course The data in the table is empty , Unqualified ;

So we need to where Medium limit , Show only a Class , The result is what you want ;

We need to pay attention to ,where You can't limit only display zhangsan01 Of course. , If you add , The last line null You can't show it .

above sql in , increase a.* To make it easier to understand , In fact, the final thing sql yes ：

select b.course_id from class_course a left join student_course b 
on a.course_id=b.course_id and a.class_id='a' and b.student_id='zhangsan01'
where a.class_id='a';

Carry out this sentence sql after ,java You can get a length of 4 Of List<String>, After traversing , Find promising null Of , It can be concluded that zhangsan01 Not finished a Conclusion of all courses in class , The requirements are realized .

5、 ... and 、 summary

1.on Conditions in , Is to decide on what to perform an associated query （on a.course_id=b.course_id） 、 What data is used for associated query （ and a.class_id='a' and b.student_id='zhangsan01'） With .
2.where Conditions in , It determines how to filter the final data （where a.class_id='a'）.
3. Please note that the conditions are written in on Or in the where in ; Such as the left-hand query in this article , Only write in on It can't meet the demand , Other data in the left table will also be displayed by connecting empty data ; therefore on Li and where Conditions need to be written in .