Is there any recommended/established way to explain a working of SQL query?
We have quite a few complex queries in our project and we need to document it using UML or any other modelling language.
Please note, I am not asking about Data Modelling. It's more about documenting SQL logic i.e how tables are connected, how conditions are affecting the outcome, joins etc.
So, finally:
Good question.
I don't have one answer, it depends on the context - how complex is the actual query, who is the audience (how proficient they are with different notations), if I will be able to present this face-to-face or is it for off-line docs, etc. Some suggestions are:
For various audience, use source-to-target mapping in a form of a spreadsheet table:
Source Column | Target Column | Transformation logic (rules + SQL)
Note that this also covers part of the data model, doesn't have to be very formal (but can be), and you can decide about the proper level of detail (capture only inputs and outputs of the whole data processing or document every step i.e. each subquery).
For other developers (who are proficient with SQL) - write well formatted and documented SQL code, with extensive use of indentation to indicate different nesting levels of sub-sub-sub-queries.
For other developers or DBAs - many mature RDBMS have a functionality of generating an explain plan (or even visual explain plan) - such output is often very helpful and carries additional information (actual execution plan with estimated cost of each step).
For academics ;-) - use relational algebra notation and draw a tree to picture query logic (example: https://people.ok.ubc.ca/rlawrenc/teaching/304/Labs/Lab1/, check Wikipedia for symbols used to represent specific operations).
Sometimes a specific tool is used to build / define data processing (for instance, Informatica or MS SSIS used to build parts of ETL logic or SAS Enterprise Guide which has a graphical interface to represent code executed) and such flow is visualized in the tool itself, however I'm not convinced that it is as expressive as SQL and can show some really complex queries well.
QBE - Query-By-Example invented by Moshé M. Zloof (available in MS Access for instance), is kind of a graphical tool / language used to define a query. But it has its limitations, too (not every query can be represented this way).
