How Do I Join Tables? Let Me Count The Ways

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.

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

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

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)