• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • How Do I Join Tables? Let Me Count The Ways

    September 1, 2015 Ted Holt

    Normalization is the process of making sure that each datum is stored in the proper table. Storing data in the wrong place gives rise to anomalies, a fancy word for problems, and you have enough problems already. To make sense of normalized data requires that tables be joined. Do you know the methods to code a join with SQL and the advantages and disadvantages of each one?

    Method 1: WHERE

    When I first learned SQL, joining was done in the WHERE clause of the SELECT statement. Here’s an example.

    select h.*, d.*
      from SalesOrderHeaders as h,
           SalesOrderLines   as d
     where h.OrderNumber = d.OrderNumber
    

    In this query, I request all columns of the sales order header table and all columns of the sales order lines. The WHERE clause tells the system to match the data on a common sales order number. That is, the header information on any row of the result set applies to one line of the same sales order.

    This syntax has its shortcomings, the greatest of which I consider to be that unmatched orders are dropped. That is, if an order has a header but no lines, or one or more lines but no header, that order does not show up in the result set. DBMS providers had to come up with extensions to support outer joins.

    I do not use this syntax because I see no advantage in it. I recommend that you know this syntax so you can recognize and understand it if you see it, but I do not recommend that you use it.

    Method 2: JOIN . . . USING

    This is an implementation of the natural join. The first thing to know about natural joins is that two tables/views are joined on common field names of the same data type.

    select *
      from SalesOrderHeaders
      join SalesOrderLines
     using (OrderNumber)
    

    OrderNumber is the common column. That is to say, the order number is named OrderNumber in both tables and is defined with the same data type in both tables.

    The second thing to know about natural joins is how they behave when you use an asterisk to select all the columns. Here’s the result set from the previous query.

    ORDERNUMBER

    ENTRYDATE

    CUSTOMERID

    LINENUMBER

    ITEMNUMBER

    QUANTITY

    1001

    2015-09-01

    102

    1

    B-1

    5

    1001

    2015-09-01

    102

    2

    B-2

    6

    1002

    2015-09-01

    103

    3

    F-3

    2

    The result set consists of:

    • the columns in USING
    • the columns of the first table that are not in USING
    • the columns of the second table that are not in USING

    This makes sense to me. Why return two columns with the same data? Would you like to see something that does not make sense to me? Look at this:

    select h.*, d.*
      from SalesOrderHeaders as h
      join SalesOrderLines   as d
     using (OrderNumber)
    

    Here’s the result set:

    ENTRYDATE

    CUSTOMERID

    LINENUMBER

    ITEMNUMBER

    QUANTITY

    2015-09-01

    102

    1

    B-1

    5

    2015-09-01

    102

    2

    B-2

    6

    2015-09-01

    103

    3

    F-3

    2

    Look, Ma! No order number. It’s as if the common columns are not part of either table.

    I rarely use the JOIN . . . USING syntax. I don’t need it, because there’s something better, namely . . .

    Method 3: JOIN . . . ON

    This is the method that does it all. You can join on whatever you want to, regardless of column name and data type.

    Here’s an example of JOIN . . . ON.

    select h.*, d.*
      from SalesOrderHeaders as h
      join SalesOrderLines   as d
        on h.OrderNumber = d.OrderNumber
    

    And here’s the result set.

    ORDERNUMBER

    ENTRYDATE

    CUSTOMERID

    ORDERNUMBER

    LINENUMBER

    ITEMNUMBER

    QUANTITY

    1001

    2015-09-01

    102

    1001

    1

    B-1

    5

    1001

    2015-09-01

    102

    1001

    2

    B-2

    6

    1002

    2015-09-01

    103

    1002

    3

    F-3

    2

    Not only can I put column names in the join expression, I can even put expressions and literals. In this example, manufacturing job number 123456, for example, matches sales order S123456.

    select m.MfgOrderNo, m.DueDate, 
            c.SalesOrderNo, c.CustomerNo
      from MfgOrdHdr as m 
      left join SalesOrdHdr as c
        on m.jobno = substr(c.SalesOrderNo,2)
    

    This is the syntax I use and recommend.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Precisely

    Participate in Precisely’s 2021 IBM i Security Survey

    Every year, Precisely asks IT pros responsible for IBM i security about their top challenges, strategies, technologies and best practices. These annual survey results provide a revealing look at the current state of IBM i security through the eyes of your peers.

    Click here to contribute your perspectives to this year’s survey.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  Free White Paper: Overcome Mobile Development Challenges
    NGS:  PAY NO LICENSE FEE for two concurrent users on Qport Office.
    System i Developer:  Session Grid Posted: RPG & DB2 Summit - Chicago, October 20-22

    Profound Hires Guru Editor; Begins IBM i Internship Program Did IBM i Just Get Hacked at DEF CON?

    Leave a Reply Cancel reply

Volume 15, Number 17 -- September 1, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL Software
Bug Busters Software Engineering

Table of Contents

  • The Path To XML-INTO Happiness, Part 3
  • How Do I Join Tables? Let Me Count The Ways
  • RCAC in DB2 For i, Part 2: Column Masks

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • 2021 Predictions for IBM i: Part Two
  • Zend Server for IBM i Now 64-Bit, Available Via RPM
  • New IBM i Logo, Publication
  • Four Hundred Monitor, January 20
  • IBM i PTF Guide, Volume 23, Number 3
  • IBM i Software And Power Systems Upgrades Keep Rolling Forward
  • Preparing For What’s Next In A Thoughtful, Structured Way
  • Guru: Fall Brings New RPG Features, Part 3
  • ARCAD Plugs IBM i DevOps Suite Into Microsoft Azure
  • Park Place Buys Curvature To Become Maintenance Goliath

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.