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
Best Way to Store Working Hours and Query It Efficiently
Count Distinct with Conditions
Grant Execute to All Stored Procedures
How to See Cakephp's SQL Dump in the Controller
How to Get the Actual Stored Procedure Line Number from an Error Message
How to Add a Column That Doesn't Allow Nulls in a Postgresql Database
Change Schema Name of Table in SQL
What SQL Coding Standard Do You Follow
What Should I Name a Table That Maps Two Tables Together
When to Use a View Instead of a Table
How to Get First Character of a String in SQL
Why No Many-To-Many Relationships
How to Loop Through a Table Variable in T-Sql
Sqlite String Contains Other String Query