Okay, this post isn’t quite about Salesforce.com today, but I think it’s a tidbit worth publishing…

I learned something about SQL today!

I love SQL — It’s the only programming language that I learnt back in my University days that is still useful. (Remember Pascal? Ha!) I especially love PostgreSQL. I did some neat procedural stuff with it earlier this year that was especially pleasing.

Anyway, today we were burdened with an overly complex query doing funky stuff with sales data. We were trying to join together two sets of data using the good old UNION statement. Result set A had 20 rows and result set B had 24 rows. When combined with UNION, the result had… not 44 rows, but 24 rows! This was a real head-scratcher until I found this in the PostgreSQL manual:

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

The kicker is that last sentence — duplicate rows are removed unless UNION ALL is used. Sure enough, we stuck in that ALL word and everything was fine.

The Bottom Line

• You learn something every year
• PostgreSQL rocks!