Please help convert Tsql "implicit joins" to explicit

Question

Sorry, I'm pretty much a SQL noob. This should work in MSFT SQL, Oracle, as well as Sybase. In the next snippet, I need to change the inner join between IJ and KL to IJ.PO_id = KL.PO_id to left join also to IJ.PO_id = KL.PO_id . So, I think I need to overestimate this. Well, implicit associations are not the most read, at least in the eyes of my colleagues. I guess I agree until I develop my own taste. Sorry, just in case, I distorted the names of tables and fields.

 /* @IJ_id is an input stored proc patrameter. */ from AB, CD, EF, GH, IJ, KL where EF.EF_id = IJ.EF_id and IJ.EF_id = AB.EF_id and EF.ZY_id = IJ.ZY_id and IJ.ZY_id = AB.ZY_id and IJ.IJ_id = AB.IJ_id and IJ.IJ_id = @IJ_id and EF.XW_id = GH.GH_id and AB.VU_code = CD.VU_code and IJ.TS > 0 and IJ.RQ = 0 and EF.RQ = 0 and AB.RQ = 0 and IJ.PO_id = KL.PO_id; 

Now my difficulty is that there is a lot going on in the where clause. Things that don't look like ab = cd remain in the where clause, but not everything that looks like ab = cd is easy to convert to an explicit join. The difficult part is that, ideally, the conditions would be between neighbors - AB+CD , CD+EF , EF+GH , GH+IJ , IJ+KL , but they are not organized now. I could reorder some, but in the end I do not want to forget about my goal: I want the new request to be no slower, and I want the new request to be no less readable. It seems to me that it might be better to hack only the part that I need to change, and leave it basically the same. I'm not sure if I can do this.

If you understand my intention, offer the best request. if you have not done so, then please tell me how I can improve the question. Thanks.

Answer 1

I think it should be something like this:

 FROM AB JOIN CD ON AB.VU_code = CD.VU_code JOIN IJ ON IJ.EF_id = AB.EF_id AND IJ.ZY_id = AB.ZY_id AND IJ.IJ_id = AB.IJ_id JOIN EF ON EF.EF_id = IJ.EF_id AND EF.ZY_id = IJ.ZY_id JOIN GH ON EF.XW_id = GH.GH_id JOIN KL ON IJ.PO_id = KL.PO_id WHERE IJ.IJ_id = @IJ_id AND IJ.TS > 0 AND IJ.RQ = 0 AND EF.RQ = 0 AND AB.RQ = 0 

I tried to arrange the tables so that the following rules were followed:

• Each join condition refers to a new table with which it joins on one side.
• No table is mentioned in the join condition if this table is not yet joined.
• Conditions in which one of the operands is a constant remains as a WHERE condition.

The last rule is difficult - from your distorted names it is impossible to say whether the condition should be part of the join or part of the where clause. Both will give the same result for INNER JOIN. Whether the condition should be part of a join or part of a where clause depends on the semantics of the relationship between the tables.

You must consider each condition in each case:

• Does a relationship between two tables define? Put it in a JOIN.
• Is this a filter by results? Put this in the WHERE clause.

Some recommendations:

• A condition that includes a parameter from the user is unlikely to be something that needs to be transferred to the connection.
• Inequalities usually do not occur in association.

Answer 2

It cannot be less readable than the example you gave ...

 from AB a join CD c on a.VU_Code = c.VU_Code join EF e on a.EF_id = e.EF_id and e.RQ = 0 join GH g on e.XW_id = g.GH_id join IJ i on a.IJ_id = i.IJ_id and e.EF_id = i.EF_id and a.EF_id = i.EF_id and e.ZY_id = i.ZY_id and a.ZY_id = i.ZY_id and i.TS > 0 and i.RQ = 0 LEFT join KL k on i.PO_id = k.PO_id where i.IJ_id = @IJ_id and a.RQ = 0 

Answer 3

Using:

  FROM AB t1 JOIN CD t2 ON t2.VU_code = t1.VU_code JOIN GH t4 ON t4.gh_id = t3.xw_id JOIN IJ t5 ON t5.ZY_id = t1.ZY_id AND t5.IJ_id = t1.IJ_id AND t5.EF_id = t1.EF_id AND t5.IJ_id = @IJ_id AND t5.TS > 0 AND t5.RQ = 0 JOIN EF t3 ON t3.ef_id = t5.ef_id AND t3.zy_id = t5.zy_id AND t3.RQ = 0 JOIN KL t6 ON t6.po_id = t5.po_id -- Add LEFT before JOIN for LEFT JOIN WHERE ab.qu = 0 

They are smoothed in the sequence of the original ANSI-89 syntax, but the order is adjusted due to a reference to an alias - cannot refer to an alias of a table until it is defined.

This is the ANSI-92 JOIN syntax - there is no performance benefit, but that means the OUTER join syntax is consistent. Just add a LEFT before “JOIN KL ...” to turn it into a LEFT JOIN.