SQL server simple query

How to select value f开发者_JAVA百科rom 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.

You might consider doing this specific "IS NULL" check in client code verses SQL because it seems a bit hacky to me... but it works.

Is this what you wanted?

The following SQL shows one way to return "its null" if the username is null OR if no row exists for int_id_user.

CREATE TABLE #tb_user (
  int_id_user INT NOT NULL PRIMARY KEY,
  username varchar(20) NULL
)
GO

/* returns NO ROWS because #tb_user contains no rows */
select coalesce(username,'its null') from #tb_user where int_id_user = 0
GO

/* returns 'its null' if username IS NULL or int_id_user does not exist in the table */
SELECT COALESCE(
    (select username from #tb_user where int_id_user = 0),
    'its null'
) AS username
GO

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

link text

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'
        )

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.

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.

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.