Finding an Accidental Cross Join in an Oracle Query

Ever work on a query that joins multiple Oracle tables and one of them is cross joining on multiple rows resulting in multiple identical data rows in your output?  Ever sit wondering like me which which table is at fault? Found a neat way to figure that out without having to actually think.

We start with something like this:

SELECT 

      list_of_fields

FROM

      table_a  a,

      table_b  b,

      table_c  c,

      table_d  d

WHERE

      blah, blah, blah

Just add this to your list of fields:

     a.rowid "A", 

     b.rowid "B", 

     c.rowid "C", 

     d.rowid "D"  

(You don't have the "A", "B", "C" and "D" if you'd rather let all the columns be called "rowid" and count column positions--which is what I actually did.)

The output looks like gibberish, but on at least one of the columns the rowid will have at least one character different from the previous row within the same set of "identical" rows. That's the table at fault.

Best wishes!