new-year

The first Oracle curiosity of 2011 !

Happy new year , dear blog readers !!!

Just to make you begin the year with your right foot, I have this very simple and free tip for you. How many times have you used cartesian products in you SQL queries ? Countless times I’m sure. But , have you ever wondered why sometimes you get unexpected results?
Let’s look at a very simple example :

select test_one.*
from (select 1 from dual where 1 = 1) test_one,
(select 2 from dual where 1 = 1) test_two
As you could expect, this query returns one column with result 1. 😎 But, what if you tried the query below :
select test_one.*
from (select 1 from dual where 1 = 1) test_one,
(select 2 from dual where 1 = 2) test_two

Our second subquery named test_two, won’t return any results. But, why can’t we see results from the first query test_one ? The reason is straightforward, although you’re joining test_one with test_two Oracle is performing a cartesian join between this two queries. What this means is, you’re just “multiplying” the result from test_one with the result from test_two. So, 1 x null = null !! Therefore, no results are outputted !!

Feel free to drop us a line on your similar experiences.

Cheers,
Carlos