Sql Select Return Default Value If Null

SQL Select Return Default Value If Null

Two things:

1. Use left outer join instead of inner join to get all the listings, even with missing pictures.

2. Use coalesce to apply the default

   SELECT Listing.Title
       , Listing.MLS
       , Pictures.PictureTH
       , coalesce(Pictures.Picture, 'default.jpg') as Picture
       , Listing.ID  
   FROM Listing 
   LEFT OUTER JOIN Pictures 
       ON Listing.ID = Pictures.ListingID 
   

EDIT To limit to one row:

SELECT Listing.Title
    , Listing.MLS
    , Pictures.PictureTH
    , coalesce(Pictures.Picture, 'default.jpg') as Picture
    , Listing.ID  
FROM Listing 
LEFT OUTER JOIN Pictures 
    ON Listing.ID = Pictures.ListingID 
WHERE Pictures.ID is null
OR Pictures.ID = (SELECT MIN(ID) 
    FROM Pictures 
    WHERE (ListingID = Listing.ID))) 

How to return default value from SQL query

Assuming the name is not nullable and that Id is unique so can match at most one row.

 SELECT 
    ISNULL(MAX(Name),'John Doe')
 FROM 
    Users 
 WHERE 
    Id = @UserId  

SQL Server how to set a default value when the column is null

Use case

select case 
          when user_Name is null then "default value" 
          else user_name 
       end  
from table

Return a default value if single row is not found

One way to do it

SELECT IFNULL(MIN(`file`), 'default.webm') `file` 
  FROM `show`, `schedule` 
 WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
   AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
 ORDER BY `start_time` DESC LIMIT 1

Since you return only one row, you can use an aggregate function, in that case MIN(), that ensures that you'll get NULL if no records selected. Then IFNULL() or COALESCE() will do its job.

Set default value in query when value is null

Use the following:

SELECT RegName,
       RegEmail,
       RegPhone,
       RegOrg,
       RegCountry,
       DateReg,
       ISNULL(Website,'no website')  AS WebSite 
FROM   RegTakePart 
WHERE  Reject IS NULL

or as, @Lieven noted:

SELECT RegName,
       RegEmail,
       RegPhone,
       RegOrg,
       RegCountry,
       DateReg,
       COALESCE(Website,'no website')  AS WebSite 
FROM   RegTakePart 
WHERE  Reject IS NULL

The dynamic of COALESCE is that you may define more arguments, so if the first is null then get the second, if the second is null get the third etc etc...

SQL - SELECT with default if null

Find the maximum of floorplan. Using the isnull function, replace floorplan by its max value, whenever it is null.

  SELECT id, 
         name, 
         price, 
         isnull(floorplan, (select max(floorplan) from Inventory) )
    FROM Inventory 
ORDER BY price

SQL Return default values if no rows returned

You can add a union all select to your existing query with default values like this:

<your existing query>

union all

select null accounts_worked, null right_contacts_made, null ppts_obtained .....
where not exists(
select *
from @tab_performance p JOIN
        dbo.TR_USR_User usr ON
            (p.usr_code = usr.usr_code) JOIN
        dbo.TR_TME_Team tme ON 
            (tme.tme_id = usr.tme_id)   
            AND p.usr_code = @usr_code
)

The where clause could be further simplified, if your inner joins don't filter out any rows from @tab_performance:

<your existing query>

union all 

select null accounts_worked, null right_contacts_made, null ppts_obtained .....
where not exists(
select *
from @tab_performance
where usr_code = @usr_code
)

SQL View - add default values if null?

You can use ISNULL:

SELECT a_case.Id,      
    ISNULL(R1.Type, 'ND') AS Referred_by_1,     
    ISNULL(R2.Type, 'ND') AS Referred_by_2,     
    ISNULL(R3.Type, 'ND') AS Referred_by_3 
FROM ...

How to set a default row for a query that returns no rows?

One approach for Oracle:

SELECT val
FROM myTable
UNION ALL
SELECT 'DEFAULT'
FROM dual
WHERE NOT EXISTS (SELECT * FROM myTable)

Or alternatively in Oracle:

SELECT NVL(MIN(val), 'DEFAULT')
FROM myTable

Or alternatively in SqlServer:

SELECT ISNULL(MIN(val), 'DEFAULT')
FROM myTable

These use the fact that MIN() returns NULL when there are no rows.

---

Related Topics