CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-databasedb2
Rong Nguyen
Rong Nguyen

Reputation: 4189

Set default value in select statement(not use UNION statement)

I would like to have a SELECT statement that will return specified default values if no rows are returned from the database.

We can use UNION to get the desired result like this question: "How to set a default row for a query that returns no rows?", but this gives an extra result row.

example: 

SELECT a 
    from TBL_TEST 
UNION 
SELECT 0 
    FROM DUAL

Is there a better way, or a standard SQL way to do this that will be portable across multiple database engines?

Upvotes: 2

Views: 17719

Answers (8)

WarrenT
WarrenT

Reputation: 4542

For a portable solution, how about:

select coalesce(a, 0)
    from TBL_TEST 
    right outer join DUAL on null is null

The COALESCE function is used here because it is more portable than NVL() or IFNULL().

You would have a DUAL table created in database systems that use a different name, such as SQL Server or DB2.

Upvotes: 5

Anvesh
Anvesh

Reputation: 7703

In, SQL SERVER 2008 R2 : When Empty String 

SELECT ISNULL(NULLIF(a,<Empty String>)<Default Value>) from TBL_TEST

e.g. SELECT ISNULL(NULLIF(a,'')0) from TBL_TEST

This is working fine...

Upvotes: 1

Anvesh
Anvesh

Reputation: 7703

In, SQL SERVER 2008 R2 : When Value IS NULL

SELECT ISNULL(a,<Default Value>) from TBL_TEST

e.g. SELECT ISNULL(a,0) from TBL_TEST

Upvotes: 1

MGPJ
MGPJ

Reputation: 1072

use the COALESCE() to convert the null value column with its default value such as

select coalesce(a,0) from TBL_TEST

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23747

In Oracle: 

select nvl(a, 0)
from DUAL left join TBL_TEST on null is null

Upvotes: 1

Vijendra Singh
Vijendra Singh

Reputation: 628

MySQL IFNULL is like oracle's NVL function 
MySQL IFNULL() takes two expressions and if the first expression is not NULL, it returns the first expression. Otherwise it returns the second expression.
Syntax 

IFNULL(expression1, expression2); 

SELECT IFNULL(a,<default value>) from TBL_TEST

Upvotes: 1

faisal
faisal

Reputation: 1339

SELECT ifnull(a,20) FROM TBL_TEST

Selects 20 if a is null otherwise selects a (in mysql, not sure about others)

Upvotes: 7

tadman
tadman

Reputation: 211600

MySQL has the DEFAULT function, but I'm not sure how standard or widely supported it is.

Upvotes: 1

Related Questions