How to Return Default Value from SQL Query

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

Returning default values in SQL queries

Just add 'HardCodeValue' as hardCodeDefaultValueColumn in your SELECT, among the other columns selected, like so:

SELECT ..., ..., 'HardCodeValue' as hardCodeDefaultValueColumn
FROM ...

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 query to return default value for a column if no Match in two different tables

Left join table1 with table2 and use coalesce to replace NULL values (when right table has no match) with the default value:

select t1.id, 
t1.name,
coalesce(t2.result, 'fail') as Result
from Table_1 as t1
left outer join Table_2 as t2 on t1.name = t2.name

How to return default row if some values do not match?

SELECT * FROM mydata
WHERE (coalesce(a, user_a), coalesce(b, user_b)) = (user_a, user_b)
ORDER BY a IS NULL, b IS NULL
LIMIT 1;

Here user_a and user_b are the values for which you query.

Return default value if no results found in SQL query

While this would probably be easier to handle with your scripting language, you could do a union all to get the default value since you're ordering by the id field:

SELECT id, picture 
FROM media
WHERE userID = $friend->user_id
AND relation = 'profile_picture'
UNION ALL
SELECT -1 id, 'default.png' picture
ORDER BY id DESC LIMIT 1
  • SQL Fiddle Demo


Related Topics



Leave a reply



Submit