Odd Inner Join Syntax and Encapsulation

Odd INNER JOIN syntax and encapsulation

The parentheses do not change the semantics. The position of the ON clause controls the order of the logical processing of joins.

First Query

SELECT Customer.Name,
       Product.Desc,
       Transaction.Date
FROM   Product
       INNER JOIN Transaction
         ON Transaction.ProductID = Product.ID
       INNER JOIN Customer
         ON Transaction.CustomerID = Customer.ID 

Second Query

(Redundant parentheses removed)

SELECT Customer.Name,
       Product.Desc,
       Transaction.Date
FROM   Product
       INNER JOIN Transaction
                  INNER JOIN Customer
                    ON Transaction.CustomerID = Customer.ID
         ON Transaction.ProductID = Product.ID 

So logically in your first example the join on Transaction, Product happens first then the virtual table resulting from that is joined onto Customer, whereas in your second example the join on Transaction, Customer happens first then the virtual table resulting from that is joined on to Product

This is only logically and as inner joins are both associative and commutative this likely won't make any difference to the execution plan (unless you add OPTION (FORCE ORDER) to the query) but it can do for outer joins.

This is covered by Itzik Ben Gan here but the article has a number of inaccuracies, see the follow up letter by Lubor Kollar as well.

How does this SQL syntax work?

First, I make it rule for clarity to never mix right and left joins in the same query. All right joins can be switched to left joins and that alone will make it easier to figure out what is going on.

Next abandon select *. It is never appropraite in a query with joins as you are returning the same data in two or more fields (the join fields) and that is wasteful of valuable network and database processing time.

I believe the wierd ONs are forcing the query to go in a particular order. They are bad and should not be used in my opinion as they are hard to maintain and hard for developers to understand as they are not common and are totally unneeded. Just reversing the right joins and putting the tables in the order you need to join them may fix this. If not you may need a few derived tables to get the right data. Note that in reversing it, you may need to change those inner joins to something else. Right now it is such a mess, it is highly likely it does not return the correct results. So in rewriting it, while you would want to see if your changes change the results, you would also want to use judgement to determine if the changes are fixes for a bad query or incorrect changes to translate the query into something maintainable.

If the developer who wrote this mess is still there, I would force him to rewrite in in more standard SQL and tell him he is forbidden to ever use the query designer again. This fails code review as far as I am concerned.

If I were rewriting this, I would look for the table that should be first in the query and work down from there. My personal guess right now is that it would be the tblDeal table but I don't know your data model so I could be wrong.

Can anyone fathom a why do this or how it works correctly with this bit of SQL?

Join expressions in SQL are constructive, just like arithmetic expressions.

That is, you can do the following:

A JOIN B ON <expr-AB>

But you can also substitute another join expression for B:

A JOIN (B JOIN C ON <expr-BC>) ON <expr-AB>

And the parens may be superfluous if the natural order of precedence would give the same result:

A JOIN B JOIN C ON <expr-BC> ON <expr-AB>

So the huge fugly query you showed is legal syntax, but it certainly could be more clear.

---

I see the query as a hierarchy like this:

so
so --left--> stfl
so --left--> stPres
so --left--> o
             o --inner-> bi
                         bi --inner-> selFac
                         bi --left--> p
             o --left--> pp
                         pp --left--> pres
                         pp --left--> tblDuck
                         pp --left--> vwDuck
             o --left--> pf
                         pf --left--> fl
             o --left--> ops

I would rewrite the query like this:

FROM tblStockChaos so 
LEFT OUTER JOIN tblWolveLocation stfl
  ON so.WolveLocationId = stfl.WolveLocationId 
LEFT OUTER JOIN vwDonald stPres
  ON so.DonaldId = stPres.DonaldId 
LEFT OUTER JOIN
  (tblChaos o 
   INNER JOIN
     (tblBillLeg bi
      INNER JOIN @Facty selFac
        ON bi.WolveId = selFac.WolveId
      LEFT OUTER JOIN tblCheeseburger p
        ON bi.CheeseburgerId = p.CheeseburgerId)
     ON o.ChaosID = bi.ChaosId 
   LEFT OUTER JOIN
     (tblDuckPez pp 
      LEFT OUTER JOIN vwDonald pres
        ON pp.DonaldId = pres.DonaldId 
      LEFT OUTER JOIN tblDuck
        ON pp.DuckID = tblDuck.DuckId 
      LEFT OUTER JOIN vwDuck
        ON pp.DuckID = vwDuck.DuckId)
     ON o.PezID = pp.PezID
   LEFT OUTER JOIN tblPezFill 
     (tblPezFill pf
      LEFT OUTER JOIN tblWolveLocation fl
        ON pf.WolveLocationId = fl.WolveLocationId)
     ON o.PezFillID = pf.PezFillID
   LEFT OUTER JOIN tblChaosCheeseburgerShipped ops 
     ON o.ChaosID = ops.ChaosID
  )

---

I can't guarantee I've got all the conditions perfect. But that's the idea.

Private vs. Public members in practice (how important is encapsulation?)

It depends. This is one of those issues that must be decided pragmatically.

Suppose I had a class for representing a point. I could have getters and setters for the X and Y coordinates, or I could just make them both public and allow free read/write access to the data. In my opinion, this is OK because the class is acting like a glorified struct - a data collection with maybe some useful functions attached.

However, there are plenty of circumstances where you do not want to provide full access to your internal data and rely on the methods provided by the class to interact with the object. An example would be an HTTP request and response. In this case it's a bad idea to allow anybody to send anything over the wire - it must be processed and formatted by the class methods. In this case, the class is conceived of as an actual object and not a simple data store.

It really comes down to whether or not verbs (methods) drive the structure or if the data does.

Incorrect syntax near SqlParameter

SQL Server parameters need to be prefixed with a @ (not a % as you've used it):

SELECT * 
FROM [ddddd] 
WHERE [Day] >= @XX AND [Day] <= @YY

and then also use the leading @ when declaring the parameters:

q.Parameters.Add(New SqlParameter("@XX", rangeFrom.Value))
q.Parameters.Add(New SqlParameter("@YY", rangeTo.Value))

How to join values of map in python?

To convert to a list will be ok.

 print(",".join(list(map(str,loc))))

In python2.x, map function return a list which return a map object in python3.x, so you neect to convert the return of map function to a list.

---

Related Topics