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

Question

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?

Solution 1

SELECT ifnull(a,20) FROM TBL_TEST 

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

Solution 2

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.

Solution 3

In Oracle:

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

Solution 4

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

Solution 5

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

select coalesce(a,0) from TBL_TEST

View more solutions

Comments

[John Woo]

what RDBMS really? all of them?

[Rong Nguyen]

yes, i use mysql, oracle and sql server :)

[Ed Plese]

This question looks similar: stackoverflow.com/questions/285666/…

[Rong Nguyen]

The solution is still use UNION, so when i have many column in select statement, i think it make poor performance :)

[Rong Nguyen]

Returns the default value for a table column ? I need default value i put in sql, not in table structure :)

[Rong Nguyen]

Thank, it is similar to ISNULL in SqlServer, NVL in Oracle, but not true :)

[Rong Nguyen]

Thank, it can use for NULL value, but can not for empty value.

[Rong Nguyen]

Thank, but it can use for NULL value, but can not for empty value

[MGPJ]

I have checked my query and it returns the empty value also in mysql

[tadman]

MySQL also has the simple IFNULL() method to convert NULL values to whatever.

[Rong Nguyen]

You create a table with no data, then check above sql, please :)

[Anvesh]

If there is empty string and first convert empty string into null using NULLIF function ..... for detail see my second answer on this question...... thanks.

[Rong Nguyen]

ok, you try to create a table with no data, and then use above sql, please :)

[Anvesh]

Well, if there is no data means table is empty in case of this database engine can't perform any function on table. Function and logic will work on Data. For empty string and null you can see my new answer thanks.... :)

[Anvesh]

ohhh. How can this possible if you find anything then please let me know :) . One more thing you can define any default value mean default constraint in Oracle, Sql Server , any . So when you send null value on that default constraint field at that time It will automatically insert default value for that null. let me know for further query.

[Rong Nguyen]

Yes, but i have three query with three default value on the same column, so i can not use default column value :)

[Rong Nguyen]

SELECT NVL(a,0) FROM DUAL, are you sure ?

[Anvesh]

so now here it's require operation on data rather than setting default value for that there should be data . thanks :)

[Rong Nguyen]

That working fine in oracle, but i am suspecting the performance compare to use UNION :-?

[WarrenT]

This does not solve the problem when there are no rows returned from the table.

[WarrenT]

The solution being sought will provide default values when no rows are returned from the table.

[WarrenT]

The solution being sought will provide default values when no rows are returned from the table.

[Rong Nguyen]

thank for your attention, so in each database system i have to use or create table like DUAL table(oracle) for each query :)

[WarrenT]

Or you could substitute the name of the equivalent table for the particular RDBMS. For example in DB2 for i, we would use SYSIBM.SYSDUMMY1.

[WarrenT]

Actually, for DB2 environments, you might use CREATE ALIAS DUAL FOR SYSIBM.SYSDUMMY1