SQL Foo | Creative Use of Null Values

One of my favorite uses of set-data manipulation involves using NULL values to my advantage; from NULLIF to COALESCE, we’ll explore some creative use of null values. These tips & tricks aren’t just a way to convert a NULL to another value, they’re a multi-purpose, insanely powerful way to massage and combine data.

NULL Galaxy - Creative use of null values
NULL Galaxy – Creative use of null values

Level Set: What is a NULL?

In SQL a NULL value isn’t a value at all – it’s lack of value. It’s a value that is indicative of not having a value. Think of it as if you asked someone a question but they didn’t respond, their response was a NULL value. So, therefore you cannot compare someone’s non-response to someone else’s non-response, while they seem like the same answer they’re likely not even the same question!

There are some exceptions to this rule (thanks, Microsoft) but, like most of my blog posts, I try to stick with ANSI standard rules. While some RDBMSs treat NULL differently and even have switches that can be set during runtime to alter how NULL logic works, we’re not going to go there. Just assume ANSI 99.

Continue reading →

Interview Question | Weed Out Non Technical Candidates

Weed out non technical candidates by asking how to open notepad.
Weed out non technical candidates by asking how to open notepad.

I’ve since evolved a little since I posted this and would like to hone my focus of this post to a clearer target: Look For Ridiculous instead.

This is my hands-down, go-to interview question to weed out non-technical candidates. I have had such great success with this interview question and have shared it with other colleagues who have experienced equal success that I think it deserves a place on this blog. I’ve talked about this before, a little over a year ago is when I made my first public announcement of my new-found favorite interview question:

How do you open notepad on windows?

I typically preface the question with a short disclaimer like, “This question is a bit unorthodox and there really isn’t a wrong answer, having more than one answer is also equally acceptable…” This sets the stage for the actual question which I’ll phrase like “On a windows machine, regardless of version specifics, how do YOU open windows notepad?”

Continue reading →