How are complex SQL queries written?

Question

SQL

When is a selection of several dozen related pages required? Even when using multiple tables, a query with a bunch of nested JOINs with parentheses looks unreadable.

Answer 1

Break the query down into views, temporary tables, stored functions returning table values, and so on.
In general, try to create some architecture. After all, the subqueries from this complex query are probably used somewhere else. Kill two birds with one stone: readability will be higher, and code reuse will be optimized.

Answer 2

Formatting is certainly good, but the person writes that he has 10 tables in the query?
It would be interesting to see what kind of query is this
. In our Valentina DB , we are developing an Object-Relational model, and in addition to tables, we have Links (connections). The engine is smart in the sense that if the relationship between two tables is unambiguous, then you can not explicitly specify the join condition. Queries become much simpler and more readable.
In addition, there is BinaryLink MM, which also allows you to reduce the number of tables by replacing the MM with a link table.

Answer 3

and what is the problem? in what you want to see in the SQL query an artistic text with meaning? )
with experience you will see and understand.
But in general, do the formatting - everything will be readable and visual.

Answer 4

Formatting and comments have not been canceled.
In combination with a clear naming of tables, their aliases and fields, this makes it possible to understand at a glance what kind of request it is and why it is.
Well, yes, cte helps not only the optimizer, but also reading the code.