Prohibit multiple table joins as described in ali development manual:

“Mandatory” is written in the manual, and I believe that many of the codes in students’ projects do not meet this requirement.

But the key question is: how to write SQL without joining? !

Decompose associated query

That is, a single table query is performed on each table to be associated, and the results are then associated in the application. The following query:

SELECT * FROM tag 
JOIN tag_post ON tag_post.tag_id=tag.id 
JOIN post ON tag_post.post_id=post.id 
WHERE tag.tag = 'mysql';Copy the code

Can be broken down into the following queries instead:

SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);Copy the code

However, this scheme also has obvious problems, that is, there may be too many parameters after IN, so it can be seen that the universality of this scheme is very limited.

Paradigm breaking

Student (ID, name), Class (ID, Description), studentClass (Studentid, Classid); Studentclassfull (studentid, class_id, name, description), studentclassfull(studentid, class_id, name, description), The name and description may have to be stored in multiple copies, but the performance of the query can be greatly improved by eliminating the need for joins.

Any specification is a compromise in a particular situation, out of this environment, not necessarily tenable.

What needs to be explained is that this kind of design out of the paradigm is the usual practice of Internet business in the design of high and published!

Case by case

Finally, it is suggested to analyze the specific problems in detail. Even though multi-table Join is not allowed by Ali specification, it is still possible to carry out multi-table Join operations in business scenarios with low concurrency such as management background.

A multi-table Join is not necessarily a Low practice, it is a Low practice in error scenarios!

Ali specification does not recommend multiple table joins, but this SQL to write?