행을 찾을 수없는 경우 값 반환 SQL

행을 찾을 수없는 경우 값 반환 SQL

---

여기 내 간단한 쿼리가 있습니다. 존재하지 않는 레코드를 쿼리하면 아무것도 반환되지 않습니다. 해당 시나리오에서 false (0)가 반환되는 것을 선호합니다. 기록이없는 단순한 방법을 찾고 있습니다.

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

---

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)

---

이것은 Adam Robinson과 유사하지만 COUNT 대신 ISNULL을 사용합니다.

SELECT ISNULL(
(SELECT 1 FROM Sites S
WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)), 0)

내부 쿼리에 일치하는 행이 있으면 1이 반환됩니다. 그런 다음 외부 쿼리 (ISNULL 포함)는이 값 1을 반환합니다. 내부 쿼리에 일치하는 행이 없으면 아무 것도 반환하지 않습니다. 외부 쿼리는 이것을 NULL로 취급하므로 ISNULL은 결국 0을 반환합니다.

---

이것은 죽은 말일 수 있습니다. 행이 없을 때 1 개 행을 반환하는 또 다른 방법은 다른 쿼리를 UNION하고 테이블에 없을 때 결과를 표시하는 것입니다.

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
) 

---

다음과 같은 것 :

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

---

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'

---

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 ...

---

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.

---

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]

---

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

---

@hai-phan's answer using LEFT JOIN is the key, but it might be a bit hard to follow. I had a much complicated query which may also return nothing. I just simplified his answer as the following for 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

참고URL : https://stackoverflow.com/questions/2679865/return-a-value-if-no-rows-are-found-sql