Recently, I have been using the H2 embedded database at work to stage financial data for processing. H2 is really convenient to set up. I don’t have to install anything on the web server. No need to start/stop the database server when deploying the application. All I need to do is to add the dependency in Maven and I’m good to go. As convenient as H2 is, I got tripped by last week by a few unexpected differences between H2 and Oracle, the database that I was using previously to stage data.
H2 Doesn’t Have Full Outer Join
H2 supports inner join, left outer join, and right outer join. Sadly, it doesn’t support full outer join at the moment. Full outer join is currently listed on H2’s roadmap. I had to perform some SQL gymnastics to come up with a new query.
H2’s Left/Right Outer Joins Are Slow
Apparently, the query optimzer for H2 isn’t as robust as other commerical RDBMS like Oracle. With 40,000 rows of record in a table with indexes, my left outer join took over 30mins to run, while an inner join took only 30s. The query execution plan didn’t reveal anything interesting, so I ended up rewriting all my queries to use inner join instead. I’m much better at SQL now!
H2 Distinguishes Null Strings and Empty Strings By Default
Oracle converts empty strings into null strings, but H2 distinguishes the two. After searching through the internet, I learned a new command, the nullif:
nullif(column, '')
nullif returns null if the value of the column is an empty string. This solves the problem nicely. After implementing this change, I discovered that I could have set H2’s mode to Oracle to treat empty strings as null string… Time to undo! However, I also learned that SQL Server distinguishes between null and empty strings as well, so this nullif will come in handy in the future.