CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
ozsenegal
ozsenegal

Reputation: 4133

SQL server simple query

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.

Upvotes: 1

Views: 355

Answers (6)

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 1

beach
beach

Reputation: 8640

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

Upvotes: 1

CodeRedick
CodeRedick

Reputation: 7415

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

link text

Upvotes: 1

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3447

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.

Upvotes: 0

slugster
slugster

Reputation: 49984

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.

Upvotes: 0

Kevin Stricker
Kevin Stricker

Reputation: 17388

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.

Upvotes: 0

Related Questions