Database independent Django queries: COALESCE vs. NVL, IFNULL, ISNULL
Most of the time, it is not necessary to write raw SQL from Django. However, there are cases where it can’t be avoided.
One common pattern in SQL that always comes up is to check two fields and get the value of the first non-NULL field. In Oracle I’ve used NVL and in MySQL I’ve used IFNULL to do this. In the MS-SQL world, the equivalent command is ISNULL. E.g., I could write the following query for Oracle:
SELECT NVL(ratings_score.score, 0) FROM ratings_score WHERE ratings_score.content_type = 1 AND ratings_score.object_id = 5
Or its equivalent for MySQL:
SELECT IFNULL(ratings_score.score, 0) FROM ratings_score WHERE ratings_score.content_type = 1 AND ratings_score.object_id = 5
However, I really don’t feel like writing database dependent SQL queries and have them embedded in my Django code. We’re using MySQL right now but we want to have the flexibility to move to PostgreSQL or Oracle in the future, if needed. So I did some digging around and found out that all the major database platforms support another command which essentially does the same thing as NVL, IFNULL and ISNULL (and more!). The command is called COALESCE and it is supported by:
- DB2: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/fcoal.htm
- Microsoft SQL Server: http://msdn.microsoft.com/en-us/library/aa258244.aspx
- MySQL: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull
- Oracle: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions023.htm
- PostgreSQL: http://www.postgresql.org/docs/8.1/static/functions-conditional.html#AEN12656 and
- SQLite: http://www.sqlite.org/lang_corefunc.html
So using COALESCE seems to be the safest way to check two (or more) fields and return the first non-NULL field from a raw SQL query in Django. I can now sleep easy and have piece of mind that if we move to a different RDBMS in the future, our code will not break. Hopefully.