How to achieve default value if column value is NULL?

Question

I want to retrieve some column values from table with these conditions.

• If value is NULL (or) Empty String , return some user defined value
• If not above condition , return it's value.

How can I figure it out ?

Here is my Table query..

CREATE TABLE AUCTION_CAR_BID(
bid_seq bigint NOT NULL AUTO_INCREMENT,
auction_car_seq bigint NOT NULL,
bid_group_seq bigint NOT NULL,
bid_price int DEFAULT 0 NOT NULL,
over_bid_price int DEFAULT -1 NOT NULL,
result_id int DEFAULT 0 NOT NULL,
remark varchar(500),
PRIMARY KEY (bid_seq)) 
ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

Here is my efforted codes to get it..

SELECT
    COALESCE(OVER_BID_PRICE, -1)
FROM
    AUCTION_CAR_BID
WHERE
    BID_SEQ = 2354435345;

Another :

SELECT
    CASE
        WHEN OVER_BID_PRICE IS NULL
        OR TRIM(OVER_BID_PRICE) = '' THEN -1
        ELSE OVER_BID_PRICE
    END OVER_BID_PRICE
FROM
    AUCTION_CAR_BID
WHERE
    BID_SEQ = 2354435345;

But I always get empty String value(not -1) if given id is not in my table.

Any suggestions would be really appreciated !

Answer 1

If you write this:

SELECT
    COALESCE(OVER_BID_PRICE, -1)
FROM
    AUCTION_CAR_BID
WHERE
    BID_SEQ = 2354435345;

The results can be two types.

First result: Your query no returns rows! Your WHERE condition is unsatisfact so you'll read NULL

Second result: Your query returns rows but the value of your field is NULL, your COALESCE works fine in this case

To resolve you can try this:

SELECT COALESCE(
   (SELECT
   COALESCE(OVER_BID_PRICE, -1)
   FROM AUCTION_CAR_BID
   WHERE BID_SEQ = 2354435345)
,-1);

Tell me if it's OK

Answer 2

How about this:

select
case when price is null or id <> 1
then -1
else price
end price
from mytable