DB2 Query Output - Different Behavior

Question

I've two tables TAB_A and TAB_B. TAB_A is master table and TAB_B is child / transaction table. TAB_A is having COL_A (Primary key) and TAB_B is having COL_B (Primary key) and also COL_A.

For some business reason, Foreign key is not defined between TAB_A and TAB_B on column COL_A.

I've four records in TAB_B with some values say 1, 2, 3 and 4 in COL_A which has got no corresponding matching values in COL_A of TAB_A. (They are orphan records, created by mistake)

When I issue the following SELECT query, I get four records

SELECT B.COL_B, B.COL_A FROM TAB_A A, TAB_B B WHERE A.COL_A = B.COL_A AND B.COL_A IN (1, 2, 3, 4)

But if I start refering A.COL_A in SELECT query, no records are returned.

SELECT B.COL_B, B.COL_A, A.COL_A FROM TAB_A A, TAB_B B WHERE A.COL_A = B.COL_A AND B.COL_A IN (1, 2, 3, 4)

Can someone please explain this weird behavior?

DB2 Version 9.5 in AIX

Answer 1

You should use an ON clause instead of a WHERE clause in your inner join. The ON clause relates to the actual join, whereas WHERE typically is used for extra conditions not relating to the join.

IBM says: "The join condition is specified after the ON keyword and determines how the two tables are to be compared to each other to produce the join result [...] Any additional conditions that do not relate to the actual join are specified in either the WHERE clause or as part of the actual join in the ON clause. "

You seem to be doing the opposite in your examples, having your join condition in a WHERE clause. AFAIK, this is not illegal, but it could explain this weird behaviour, when used with a SELECT clause that only references columns from one of the tables.

Answer 2

I looked at the documentation in IBM site. Though they talk about using "JOIN", there is also a mention of using direct join using "WHERE" conditions (which I used) and mentioned that they should produce same result.

Also, I've previously worked in Oracle and SQL Server. The above syntax just worked fine. Still not sure why output differs, just because there is an additional column added in SELECT clause

Answer 3

Both queries should return the same rows. If this really behaves as you describe, you have found a bug in DB2.

What are you trying to accomplish with this query? If the values (1,2,3,4) of B.COL_A are orphan records, then this query should return no rows. If you meant to be searching for the orphans, you probably need to do some sort of outer join.