SQL can do more than you remember

(You can watch the video version of this post on YouTube.)

Now if you study computer science, you take a databases class, right? I guess there are trends and there are different ways to focus it, but still everybody learns SQL. SELECT, FROM, WHERE, GROUP BY, HAVING. Nested queries are also commonly taught, I assume. Then you get things like UNION and INTERSECT, ALL and ANY, IN and NOT IN.

But here are some things, and maybe you’ll say “oh of course, didn’t you know that?” .. but here are some things that I didn’t know until fairly recently. Look stuff up. If there is a thing you wish you were allowed to write, maybe you actually are! Two examples.

COALESCE is a bit of syntactic sugar, it returns the first non-null value in its arguments. Not something you couldn’t work around, but a nice way to handle defaults, for example.

But here’s something I wouldn’t know how to do at all, at least not without significant chicanery. The LAG expression lets you look at preceding rows, so for example this query here gives you a “difference” column.

SELECT year,
       amount,
       amount - LAG(amount,1) OVER(ORDER BY year) AS increase_since_last_year
FROM ...

You could script your way around this, of course, but this way you have a single SQL query that actually says what you mean and you stay within the DBMS, so with a bit of luck it’s more efficient than doing it yourself.

So check out what’s possible in SQL, you might be surprised!