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
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.