'Return a value if no rows are found in Microsoft tSQL
Using a Microsoft version of SQL, here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that false (0) is returned in that scenario. Looking for the simplest method to account for no records.
SELECT CASE
WHEN S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1) THEN 1
ELSE 0
END AS [Value]
FROM Sites S
WHERE S.Id = @SiteId
Solution 1:[1]
SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END AS [Value]
FROM Sites S
WHERE S.Id = @SiteId and S.Status = 1 AND
(S.WebUserId = @WebUserId OR S.AllowUploads = 1)
Solution 2:[2]
This is similar to Adam Robinson's, but uses ISNULL instead of COUNT.
SELECT ISNULL(
(SELECT 1 FROM Sites S
WHERE S.Id = @SiteId and S.Status = 1 AND
(S.WebUserId = @WebUserId OR S.AllowUploads = 1)), 0)
If the inner query has a matching row, then 1 is returned. The outer query (with ISNULL) then returns this value of 1. If the inner query has no matching row, then it doesn't return anything. The outer query treats this like a NULL, and so the ISNULL ends up returning 0.
Solution 3:[3]
This might be a dead horse, another way to return 1 row when no rows exist is to UNION another query and display results when non exist in the table.
SELECT S.Status, COUNT(s.id) AS StatusCount
FROM Sites S
WHERE S.Id = @SiteId
GROUP BY s.Status
UNION ALL --UNION BACK ON TABLE WITH NOT EXISTS
SELECT 'N/A' AS Status, 0 AS StatusCount
WHERE NOT EXISTS (SELECT 1
FROM Sites S
WHERE S.Id = @SiteId
)
Solution 4:[4]
Something like:
if exists (select top 1 * from Sites S where S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1))
select 1
else
select 0
Solution 5:[5]
I read all the answers here, and it took a while to figure out what was going on. The following is based on the answer by Moe Sisko and some related research
If your SQL query does not return any data there is not a field with a null value so neither ISNULL nor COALESCE will work as you want them to. By using a sub query, the top level query gets a field with a null value, and both ISNULL and COALESCE will work as you want/expect them to.
My query
select isnull(
(select ASSIGNMENTM1.NAME
from dbo.ASSIGNMENTM1
where ASSIGNMENTM1.NAME = ?)
, 'Nothing Found') as 'ASSIGNMENTM1.NAME'
My query with comments
select isnull(
--sub query either returns a value or returns nothing (no value)
(select ASSIGNMENTM1.NAME
from dbo.ASSIGNMENTM1
where ASSIGNMENTM1.NAME = ?)
--If there is a value it is displayed
--If no value, it is perceived as a field with a null value,
--so the isnull function can give the desired results
, 'Nothing Found') as 'ASSIGNMENTM1.NAME'
Solution 6:[6]
You only have to replace the WHERE with a LEFT JOIN:
SELECT CASE
WHEN S.Id IS NOT NULL AND S.Status = 1 AND ...) THEN 1
ELSE 0
END AS [Value]
FROM (SELECT @SiteId AS Id) R
LEFT JOIN Sites S ON S.Id = R.Id
This solution allows you to return default values for each column also, for example:
SELECT
CASE WHEN S.Id IS NULL THEN 0 ELSE S.Col1 END AS Col1,
S.Col2,
ISNULL(S.Col3, 0) AS Col3
FROM
(SELECT @Id AS Id) R
LEFT JOIN Sites S ON S.Id = R.Id AND S.Status = 1 AND ...
Solution 7:[7]
This Might be one way.
SELECT TOP 1 [Column Name] FROM (SELECT [Column Name] FROM [table]
WHERE [conditions]
UNION ALL
SELECT 0 )A ORDER BY [Column Name] DESC
Solution 8:[8]
No record matched means no record returned. There's no place for the "value" of 0 to go if no records are found. You could create a crazy UNION query to do what you want but much, much, much better simply to check the number of records in the result set.
Solution 9:[9]
DECLARE @col int;
select @col = id FROM site WHERE status = 1;
select coalesce(@col,0);
Solution 10:[10]
What about WITH TIES?
SELECT TOP 1 WITH TIES tbl1.* FROM
(SELECT CASE WHEN S.Id IS NOT NULL AND S.Status = 1
AND (S.WebUserId = @WebUserId OR
S.AllowUploads = 1)
THEN 1
ELSE 0 AS [Value]
FROM Sites S
WHERE S.Id = @SiteId) as tbl1
ORDER BY tbl1.[Value]
Solution 11:[11]
@hai-phan's answer using LEFT JOIN is the key, but it might be a bit hard to follow. I had a complicated query that may also return nothing. I just simplified his answer to my need. It's easy to apply to query with many columns.
;WITH CTE AS (
-- SELECT S.Id, ...
-- FROM Sites S WHERE Id = @SiteId
-- EXCEPT SOME CONDITION.
-- Whatever your query is
)
SELECT CTE.* -- If you want something else instead of NULL, use COALESCE.
FROM (SELECT @SiteId AS ID) R
LEFT JOIN CTE ON CTE.Id = R.ID
Update: This answer on SqlServerCentral is the best. It utilizes this feature of MAX - "MAX returns NULL when there is no row to select."
SELECT ISNULL(MAX(value), 0) FROM table WHERE Id = @SiteId
Solution 12:[12]
You should avoid using expensive methods. You don't need any column for TBL2.
SELECT COUNT(*) FROM(
SELECT TOP 1 1 AS CNT FROM TBL1
WHERE ColumnValue ='FooDoo') AS TBL2
Or this code:
IF EXISTS (SELECT TOP 1 1 FROM TABLE1 AS T1
WHERE T1.ColumnValue='VooDoo')
SELECT 1
ELSE
SELECT 0
Solution 13:[13]
You can do something just like this.
IF EXISTS ( SELECT * FROM TableName WHERE Column=colval)
BEGIN
select
select name ,Id from TableName WHERE Column=colval
END
ELSE
SELECT 'test' as name,0 as Id
Solution 14:[14]
My solition is working
can testing by change where 1=2 to where 1=1
select * from (
select col_x,case when count(1) over (partition by 1) =1 then 1 else HIDE end as HIDE from (
select 'test' col_x,1 as HIDE
where 1=2
union
select 'if no rows write here that you want' as col_x,0 as HIDE
) a
) b where HIDE=1
Solution 15:[15]
I have a union query with 6 queries. They all match off of a number in 1 table. If there is no value in one of the queries it will not show any result.
Solution 16:[16]
I liked James Jenkins reply with the ISNULL check, but I think he meant IFNULL. ISNULL does not have a second parameter like his syntax, but IFNULL has the second parameter after the expression being checked to substitute if a NULL is found.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow