News for hackers. Stuff that matters.
Re:Our SQL interview questions by jcampbell1 on Tuesday May 28, 2013 @09:33AM (#5779885)
What is the preferred way to aggregate with nulls?

    SELECT  Departments.name, SUM(COALESCE(salary,0))
    FROM Departments 
    LEFT JOIN employees USING departmentID
    GROUP BY 1
The above is how I would solve the last one, but I often feel like I abuse COALESCE.
Re:Our SQL interview questions by a8m88 on Tuesday May 28, 2013 @09:44AM (#5779969)
Using COALESCE here is correct, but sum() returns NULL if there were no non-NULL inputs, so COALESCE should go around it:

COALESCE(sum(salary), 0)

Re:Our SQL interview questions by zrail on Tuesday May 28, 2013 @09:36AM (#5779902)
Aggregates generally do the right thing with null without the coalesce.
Re:Our SQL interview questions by jcampbell1 on Tuesday May 28, 2013 @10:01AM (#5780067)
Thanks. It appears I need to stop overusing coalesce. I was told that sql NULL means "A value that is not yet known", which nicely explains why 1+NULL, 1 < NULL, 1 > NULL, 1 = NULL is always NULL. Now I know that AVG(test_scores) produces the average of the known values automatically.

- - -

I just did a test, and it appears the COALESCE is needed in this case. Running an aggregate where all values are null, results in NULL (the empty department). You need to do something because the total salary of an empty department is known to be zero.

Re:Our SQL interview questions by dragonwriter on Tuesday May 28, 2013 @01:17PM (#5781223)
> Aggregates generally do the right thing with null without the coalesce.

Aggregates generally do the most-likely-to-be-right thing with NULL values if there is at least one non-null input to the aggregate. The thing is, if you depend on this, you'll run into real data situations where all the inputs are NULL, the result is NULL, and that's not what you expected.

If you are aggregating over an expression that can be NULL, and you always want a non-NULL answer, you probably need to use coalesce or something similar so that you don't have non-NULL inputs to the aggregate.