Comparison between SQL Cartesian product query and correlated query performance

First of all, SQL will be used to understand, not experts, the following are working experience, talk to lyric.

Today to help verify the port of colleagues published, look at the relevant SQL content, found that its use of SQL statements will lead to Cartesian product phenomenon, in order to help explain the following analysis:

Student table:

Teacher Table:

Course Table:

Student_course table:

SQL1 query statements similar to the problem found:

SELECTd.st_name,d.class_id,d.st_id fromCourse asA, Student_course asB, Teacher asC, Student asDWHEREa.cu_id=b.cu_id andb.st_id=d.st_id andc.dep_id=d.dep_id andA.cu_name= 'English';

The SQL2 statement with the inner association:

SELECT     Student.st_name,    student.class_id,    from    course    JOIN student_course USING (cu_id)     JOIN student USING (st_id)     JOIN teacher USING (dep_id) WHERE  =' english ';

Execution time comparison (has been verified several times):

SELECTd.st_name,d.class_id,d.st_id fromCourse asA, Student_course asB, Teacher asC, Student asDWHEREa.cu_id=b.cu_id andb.st_id=d.st_id andc.dep_id=d.dep_id andA.cu_name= 'English'>OK>Time:0. 002sSELECTStudent.st_name, student.class_id, student.st_id fromCourseJOINstudent_course USING (cu_id)JOINstudent USING (st_id)JOINteacher USING (dep_id)WHERECourse.cu_name= 'English'>OK>Time:0.001s

Analysis Reason:

When we do not add course.cu_name = ' English ' This constraint, we compare the contents of the query results, as shown below SQL1 query results:

SQL2 Query Results:

You can see that SQL1 results have more fields than SQL2, and when there is a large amount of data or more related table fields, there is a significant performance difference between conditional queries through where, so it is recommended that SQL be written with the use of related methods to improve performance.

Just a little experiment, a detailed explanation can refer to the sticker: https://www.cnblogs.com/alianbog/p/5618349.html

Please forgive me for stealing a picture.

Comparison between SQL Cartesian product query and correlated query performance