This is a collection of tips and tricks; solutions I couldn’t find to problems I’m sure others must have. Mostly they relate to MySQL, but if you watch closely you may see some SQL Server pointers appear. *

The greatest of null

Imagine you have two fields in a query and you want to retrieve whichever is the larger. I commonly want to do this with two date fields, created_date and modified_date, but until recently I was retrieving both and doing a manual comparison on the result. Why?

Well, MySQL’s GREATEST command is designed for doing just this, but if one of the fields contains a NULL value, the result will always be NULL. Counter-intuitive, but that’s how it works. And while every record in my dataset will have been created, many will not have been modified.

The answer is to pre-qualify the fields so they won’t be NULL when GREATEST looks at them. You can do this with the COALESCE function. COALESCE simply returns the first non-null value in a list, so COALESCE(created_date, modified_date) should always return the created date and COALESCE(modified_date, created_date) will return the modified date unless it’s null in which case it will, like its predecessor, return the created date.

So now I can reliably retrieve the most recent date from each record with

SELECT GREATEST(COALESCE(created_date, modified_date), COALESCE(modified date, created_date))

Inner joins, multiple tables

Why does the following query cause an error?

SELECT * FROM a, b INNER JOIN c ON a.id = c.id WHERE a.id = b.id

No, I didn’t know either, but I do now. Apparently it’s down to Operator Precedence. Because of the preceding comma, MySQL treats b INNER JOIN c on a.id = c.id as a single statement, and once you know that you can see the problem. a.id: Where’s my table, dude?

The good news: that default behaviour is easily changed by forcing MySQL to evaluate the FROM clause as one statement. We get a working query just by putting our initial tables in parentheses:

SELECT * FROM (a, b) INNER JOIN c ON a.id = c.id WHERE a.id = b.id