CODEWITHSUNDEEP
sqlt-sqlsubquery
cobaltduck
cobaltduck

Reputation: 1598

Return a value, even when a sub-query in a where returns empty set

I have a query that is a little like this:

SELECT ISNULL(MyColumn, "Not Applicable") As MyColumn
FROM MyTable
WHERE SomeOtherColumn =
(SELECT AValue
FROM SomeOtherTable
WHERE SomeConditionHolds
)

If the relevant value for MyColumn has a value, I get this, and if it is null, this gives me "Not Applicable" as desired.

However, occasionally the sub-query returns empty set, in which case the whole query returns empty set also. I would still like it to return "Not applicable."

The best I have been able to do is add to the above

UNION
SELECT 'Not Applicable' AS MyColumn
WHERE NOT EXISTS
    (SELECT AValue
FROM SomeOtherTable
WHERE SomeConditionHolds
)

But this feels really hacky and inefficient. I hope someone has a better idea.

Upvotes: 2

Views: 5307

Answers (5)

HABO
HABO

Reputation: 15816

declare @Foo as Table ( FooId Int Identity, Something VarChar(16) );
insert into @Foo ( Something ) values
  ( 'One' ), ( 'II' ), ( '3' );

declare @FooLimit as Int = 1; -- Try 5.

with Alicia as (
  select FooId, Something, 1 as SetId
    from @Foo
    where FooId >= @FooLimit
  union all
  select 42, 'n/a', 2 )
  select FooId, Something
    from Alicia
    where SetId = ( select Min( SetId ) from Alicia )

Upvotes: 0

Bohemian
Bohemian

Reputation: 425003

Since your subquery returns one value, you can easily convert this to an outer join, which will always give you a row and which will also make the query more efficient and elegant:

SELECT
    ISNULL(MyColumn, "Not Applicable") As MyColumn
FROM MyTable
LEFT JOIN SomeOtherTable
    ON SomeOtherColumn = AValue
    AND SomeConditionHolds

The key thing here is that SomeConditionHolds is in the join condition, not the where clause, otherwise the left join effectively becomes an inner join.

Upvotes: 0

G. Stoynev
G. Stoynev

Reputation: 7783

SELECT ISNULL(MyColumn, DummyCol) As MyColumn
FROM MyTable 
  RIGHT OUTER JOIN (SELECT 'Not Available' AS DummyCol) Q ON DummyCol IS NOT NULL
   AND SomeOtherColumn =
     (SELECT AValue
     FROM SomeOtherTable
     WHERE SomeConditionHolds
     )

Upvotes: 1

Behrouz Bakhtiari
Behrouz Bakhtiari

Reputation: 781

Try this : 

    SELECT Case When T.AValue Is Null Then 'Not Applicable' Else MyColumn End As MyColumn
    FROM MyTable Left Outer Join 
   (SELECT AValue
    FROM SomeOtherTable
    WHERE SomeConditionHolds
   ) T on MyTable.SomeOtherColumn  = T.AValue

Upvotes: 0

mojtaba
mojtaba

Reputation: 339

get Query result and when no Row exist set result with "Not Applicable" otherwise you must double check and waste system time.

Upvotes: 0

Related Questions