SQL Cross Join Gotcha


I was fixing a bug in our product sometime back. It was related to data migration . As we had revamped the functionality altogether the data fom the previous version of the product wasn’t showing up at all in the new reporting interface.

There were new tables added inorder to implement the new functionality so the reporting queries were also modified so that these tables were taken into account. As I was going through the code I found the specific query which wasn’t returning any results.

The query was a page long so I decided to follow the rule of elimination (gradually removing the conditions from the where clause) to find out the issue. I started removing the conditions and in the end I was left with a simple cross join of 5 tables. But still the query wasn’t returning any data.

Then I started checking the contents of the table and it turned out that the new table added didn’t had any row at all. So the cross join wasn’t returning any data since the last table was empty.

So don’t be surprised when a cross join doesn’t return any data. Make sure that you are not taking a join with an empty table 🙂

Advertisements
SQL Cross Join Gotcha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s