Why COALESCE, NULLIF and CAST are used?-CodePudding

Can anyone please let me know why the following line is used in the query?

SELECT user_id 
    COALESCE(NULLIF(CAST(user_id AS VARCHAR(10)) ,'0'), '')
FROM users

user_id is of type int. But if I run the following, it shows the same output as above.

SELECT user_id FROM users

CodePudding user response：

Let's check it out by parts:

COALESCE(NULLIF(CAST(user_id AS VARCHAR(10)) ,'0'), '')

COALESCE is a method useful when you want ensure some value instead NULL imagine some query and a specific column will be used to a math operation, you will prefer receive 0 instead of NULL. So COALESCE(USE THIS VALUE IF IS NOT NULL, OR USE THIS)

NULLIF check two arguments, and return null in the case they have same value, otherwise return value of first argument

CAST is a method to transform the type of something into other type, such the integer 1 into string "1", remember 1 is not equal to "1"

COALESCE(NULLIF(CAST(user_id AS VARCHAR(10)) ,'0'), '')

means, if the converted to string value of user_id is '0', I want this to evaluate to '', otherwise return it transformed into a string value.

CodePudding user response：

Strictly speaking, the answer to "why it's used" is "it doesn't have to be used", because there's a way to express it that's easier to understand and performs better.

Your observation that the output is the same is incorrect: It looks the same, but it isn't the same because user_id is an integer in the table and the simple query out, but varchar(10) in the complicated query output.

This produces equivalent output:

SELECT
  CASE
    WHEN user_id = 0 THEN ''
    ELSE CAST(user_id AS VARCHAR(10))
  END AS user_id
FROM users

And needs no explanation to understand.