Another Reason To Use Unrequired Correlation Names

Almost eight years ago I gave you two good reasons to use a correlation name when SQL did not require one. A recent experience revealed to me another good reason to use an unnecessary correlation name, and I am happy to be able to share that information with you today!

A colleague of mine had an SQL query that ran to completion but produced the wrong results. He asked me to take a look at it, and I’m glad he did, because the experience taught me something. Let me set up the situation for you.

Assume two tables: a table of customer orders, and a table of invoices. (I’ve omitted most of the columns (fields) such tables would have in order to shorten the code.)

create table corders
  (  COrderNumber     dec(7),
     CInvoiceNumber   dec(7),
    primary key (COrderNumber))

create table invoices
  (  InvoiceNumber    dec(7),
     InvoiceDate      date,
   primary key (InvoiceNumber))

insert into COrders values
(1,3),(2,1),(3,4),(4,2),(5,null),
(6,null),(7,5),(8,null),(9,null),
(10,null)

insert into invoices values 
(1,'2015-01-31'), (2,'2015-01-31'),
(3,'2015-02-01'), (4,'2015-02-02'),
(5,'2015-02-02')

His query was something like this:

select * from COrders
  where CInvoiceNumber in
   (select CInvoiceNumber
      from invoices)

Do you see the problem with this statement? If not, why not study it until you do?

The query runs, and believe it or not, produces the correct results using my test data.

CORDERNUMBER  CINVOICENUMBER
============  ==============
          1              3  
          2              1  
          3              4  
          4              2  
          7              5  

However–and this is a big however–the query gave the correct results by pure coincidence. In my colleague’s case, the result set was obviously wrong.

In case you didn’t find it, the problem is that the subquery refers to a column–CORDERNUMBER–that is not found in the INVOICES table. SQL allows this, but not without a warning. In the green-screen Start SQL Interactive Session (STRSQL) command, you get this message:

                             Confirm Statement

You have entered a subquery that contains a correlation
without qualification for:
  Field. . . . . . . . . . . . .:    CINVOICENUMBER


Press Enter to confirm your SELECT statement.
Press F12=Cancel to return and cancel your SELECT statement.

If you bother to look at the preprocessor listing, an RPG program with embedded SQL produces similarly-worded message SQL0012. But who looks at preprocessor listings when the compilation succeeds? I shamefully admit that I rarely, if ever, do.

Suppose my friend had used correlation names to qualify the column references, like this:

select c.*
 from COrders as c
  where c.CInvoiceNumber in
   (select i.CInvoiceNumber
      from invoices as i)

The GUI tool he was using would have heartlessly responded with message SQL0205, Column CINVOICENUMBER, not in table INVOICES in SOMELIB. My colleague would have had no choice but to fix the query.

Here’s the query as he should have written it, with correlation names.

select c.*
 from COrders as c
  where c.CInvoiceNumber in
   (select i.InvoiceNumber 
      from invoices as i)

I probably overuse qualification by correlation names, but using unnecessary qualification has never got me into trouble, and I can always find better things to do than debug SQL queries.

RELATED STORY

Reasons to Use Unrequired Correlation Names