0

So I was looking at this question and I am trying to figure out if Coalesce is used correctly for the answer. 

INSERT INTO users (user_id, name)
SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';

I apologize if this is the wrong way to ask this question but I didn't have enough rep to comment on that 5 year old post. My confusion steams from the use of Coalesce in this answer. Coalesce compares two or more columns and takes the first NON Null value from them, if I am correct in understanding.

This user's issue was if a value didn't exist, 'Bob', then his user_id field would be 0 on insert, which he didn't want because it needed to start at 1.

So am I understanding coalesce correctly in thinking it's not needed here at all given that even in the answer he simple put a 0 for the second argument. It seems that Coalesce in this example yields nothing and really the work horse is the 'Select 1 + Select max()'.

Again I am just trying to understand the concept of Coalesce since I ran across this as an example to a problem I was having.

Community
  • 1
  • 1
NitroFrost
  • 133
  • 4
  • 20

1 Answers1

1

The query:

SELECT max(user_id) FROM users WHERE name='Bob'

Would return NULL if there was no 'Bob'.

So COALESCE() is used to return 0 instead of NULL in that case, and then add 1 to get the next if there was a 'Bob', or 1 if there wasn't.

You are correct about COALESCE() taking the first non-null value from a list of values.

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • Ok so the Coalesce(x,0) the 0 in that is meant to be the compared value. Is there a pro or con to using IFNULL() instead of Coalesce in this situation. It seems still that Coalesce is being misused to me but I understand how it is working now by your explanation. – NitroFrost Nov 12 '14 at 18:50
  • 1
    `COALESCE()` is in all the major databases, while `IFNULL()` isn't in SQL Server which uses `ISNULL()` instead. Performance should be no different. – Hart CO Nov 12 '14 at 18:56