The query you posted:
Select * from table1, table2 where table1.XID=table2.XID
should produce the five-row result set (with duplicates) that you want. It does when I run it in Oracle 10. To get the three-line results, I have to say “SELECT DISTINCT …”.
The opposite of “SELECT DISTINCT” is “SELECT ALL”. I’ve never used it before, because it’s always been the default, but it could solve your problem. Try:
Select ALL * from table1, table2 where table1.XID=table2.XID