Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

Comment on Practical SQL: Three Ways to JOIN

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Comment on Practical SQL: Three Ways to JOIN

    Article at Practical SQL: Three Ways to JOIN

    Our tables tend to have unique column names (old school!) so I wasn't aware of the disappearing columns when using USING. Thanks for that.


    You can cut down on the typing a little by using correlation names instead of the full table names:

    select * from ORDERS H join ORDERLINES D
    on H.ORDERNO = D.ORDERNO
    where H.ORDERNO = 123456

  • #2
    Originally posted by DaleB63 View Post
    select * from ORDERS H join ORDERLINES D
    on H.ORDERNO = D.ORDERNO
    where H.ORDERNO = 123456
    You're absolutely right, Dale. Correlations are an important part of several SQL functions. Hopefully I can write an article focused on correlations and their uses.

    Comment


    • #3
      Sometimes you can use a sub-select that produces the same result. Granted this is NOT a join but
      with SQL there's never just one way...Lots of ways. Be sure to check your statements performance
      with SQL Explain as some methods may perform better than others. The example below would "join"
      all matching h.orderno with any sub-selected d.orderno first...Then return the single order 123456
      from that set.

      However switch the "where" and the "and" operands, and then you first select the single h.orderno
      123456 from orders h, then the sub-select returns only the matching row from orderlines d. Obviously
      performance would be better with the latter.

      select * from orders h
      where (h.orderno = (select d.orderno from orderlines d))
      and (h.orderno = 123456)

      select * from orders h
      where (h.orderno = 123456)
      and (h.orderno = (select d.orderno from orderlines d))

      Comment


      • #4
        Using "USING" to join a couple of tables under the rare circumstance of a truly "normalized" database having the same column names across all related tables for a data entity...is well rare. I've personally never seen one. And even if you had a DB like that...under what circumstances would you ever want any/all columns except the "key" fields used to join? A rhetorical question, I don't expect an answer. It just makes me wonder sometimes what "ideas" are in play when an enhancement like "USING" is implemented.

        Comment


        • #5
          Apologies...I totally spaced my prior post. A sub-select must only return one row...I was looking at something else
          when a crisis occurred and a bomb dropped. My bad!

          Comment


          • #6
            Actually, the application I use now uses the same column name across tables..

            But it was built using CA Plex, which in the generated RPG code always renames the external columns...so I got lucky there.

            However, even with handcoded RPG, my new stuff uses the same column names across tables.

            So using is great from my point of view. Also great if your porting an app. It's only tables defined for old style RPG that have issues.

            The biggest problem is at at v5r4, USING has a bug. It's fixed in 6.1 and higher. Joe touched upoon it in his article, but he should have highlighted it a bit more IMO.

            At v5r4,
            SELECT * from tableA A join tableB B using (KEY1, KEY2)
            gives you KEY1 and KEY2 from both files. So for instance if you where ordering on KEY1, KEY2, you have to qualify the name:
            SELECT * from tableA A join tableB B using (KEY1, KEY2)
            ORDER BY A.KEY1, A.KEY2

            At 6.1 and higher, only one copy of the key fields are present...and if you reference them they must be unqualified.
            So this which works at v5r4 will fail at 6.1 or 7.1
            SELECT * from tableA A join tableB B using (KEY1, KEY2)
            ORDER BY A.KEY1, A.KEY2

            At 6.1 or 7.1 it needs to be
            SELECT * from tableA A join tableB B using (KEY1, KEY2)
            ORDER BY KEY1, KEY2

            Moral of the story:
            Do not use USING in production code at v5r4...

            Charles

            Comment

            Working...
            X