SQL server simple query

Question

How to select value from a table if it value is not null,and select a default value if not?.I.E:

select coalesce(username,'its null') from tb_user where int_id_user = 0

I try to run this query but its not working,i expect to get 'its null' since int_id_user is 0,but it returns NULL.

Answer 1

select isnull(column, 'default value')
from table

link text

Answer 2

Telos has it right. Coalesce works like isnull for N columns.

COALESCE(column1, column2, 'default value')

Is what you'd want to use if you had more than one column to try before using a default.

Your current query would return 1 because 1 is never null.

Answer 3

If you are using SQL Server (i won't speak for other dbs), then that won't return the result you want anyway.

The syntax is:

coalesce(expr1, [expr n,] expr2)

In your case, you are saying "if the int value 1 is null then return me the text 'not null'". But the value of 1 can never be null, so you'll never get the desired text.

Answer 4

No, this wont work if there is no row being returned - isnull and coalesce will work if there is a null value in the row, but there should be a row to begin with. If there are no rows, then nothing is returned.

If you are using a stored procedure, it might be better to check the rowcount that the query returns and put some default value whenever it is zero.

Also isnull/coalesce will have to be on some column rather than the integer 1.

Answer 5

SELECT  COALESCE(username, 'its null'), COALESCE(user_last_name, 'its also null')
FROM    (
        SELECT  0 AS param
        ) q
LEFT JOIN
        tb_user
ON      int_id_user = param

or, if you only want one field,

SELECT  COALESCE
        (
        (
        SELECT  username
        FROM    tb_user
        WHERE   int_id_user = 0
        ),
        'its null'
        )