Union visualization methods

Question

It’s just interesting if anyone has any tricks (or tools) that they use to render connections. You know that you are writing the perfect query, jogging, and after it works for 20 minutes, you realize that you have probably created a Cartesian connection.

It’s sometimes difficult for me to understand what will happen when I add another expression about a connection and wonder if people use different methods that they use when trying to assemble many connections.

Answer 1

Always remember the end.

• Determine which columns you need.

• Try to find out the minimum number of tables that will be required for this.

• Write your FROM part with a table that will give the maximum number of columns. for example from groups T

• Add each connection one by one to a new line. Make sure that at each step you will need an INTERNAL, INTERNAL, LEFT, RIGHT JOIN.

Usually works for me. Keep in mind that this is a structured query language. Always split your query into logical lines, and this is much easier.

Answer 2

Each union combines two result sets into one. Each of them can be from one database table or temporary result set, which is the result of the previous join (s) or subquery. Always know the order in which the connections are processed, and for each connection, know the nature of the two temporary result sets with which you are joining. Know what kind of logical entity each row in this result set represents and which attributes in this result set uniquely identify this object. If your connection is designed to permanently join one line to one line, these key attributes are the ones you need to use (in join conditions) to implement the join. If your connection is intended to create a kind of Cartesian product, then it is important to understand above to understand how the connection conditions (regardless of what they are) will affect the power of the new combined result set.

Try to be consistent in using external connection directions. I try to always use Left Joins when I need an external join, because I “think” of each join as “joining” a new table (on the right) to what I already joined (on the left) from the Left Join expression ...

Answer 3

Run an explanation plan.

These are always hierarchical trees (for this you need to do this first). There are many tools for creating these plans in graphical trees, some of them in SQL browsers (for example, Oracle SQLDeveloper, any SQlServer GUI client). If you don’t have a tool, most text extensions include a “depth” column, which you can use to indent lines.

What you want to find is the cost of each line. (Note that for Oracle, however, higher costs can mean less time if it allows Oracle to make a hash join rather than nested loops, and if the final result set has high power (many, many lines).)

Answer 4

I have never found a better tool than to think about it and use my own mind.

If the request is so complex that you cannot do this, you can use either CTE or views or some other carefully organized subqueries to break it down into logical parts so that you can easily understand and visualize each part, even if you cannot manage everything.

In addition, if your task is efficiency, then SQL Server Management Studio 2005 or later allows you to get estimated query execution plans without actually executing the query. This can give you very good ideas on where the problems are if you are using MS SQL Server.