CODEWITHSUNDEEP
sql-serversubquery
Fobatai
Fobatai

Reputation: 67

If subquery returns no rows, return 0 else return value of the subquery

I got a question and you can most likely help me. I have got a query with a subquery that narrows the search. The query ends with this.

SELECT 
    * 
FROM 
    Purchase
WHERE
    Total < 5000
AND 
    Total > 
            (
                SELECT TOP 1
                   buyamount
                FROM
                   employee
                WHERE
                   manager = 'TST'
                ORDER BY
                   buyamount ASC
            )
ORDER BY Date DESC

I want the value of the subquery after the ">"... but if the subquery gives nothing in return, i want it to be 0. How do i do this?

Upvotes: 2

Views: 4633

Answers (1)

Chris Albert
Chris Albert

Reputation: 2507

In the example you posted you could simply wrap the subquery in COALESCE and get your desired result.

SELECT 
    * 
FROM 
    #Purchase
WHERE
    Total < 5000
AND 
    Total > COALESCE(
            (
                SELECT TOP 1
                   buyamount
                FROM
                   #employee
                WHERE
                   manager = 'TST'
                ORDER BY
                   buyamount ASC
            ), 0)
ORDER BY Date DESC

You could also use a variable in your provided example instead of a subquery. I prefer this approach because it separates the logic, making it easier to read. Subqueries can also be a performance killer. Having it broken out like this, it should perform better.

DECLARE @BuyAmount INT
SET @BuyAmount = (SELECT TOP 1 BuyAmount FROM #Employee WHERE Manager = 'TST' ORDER BY BuyAmount ASC)
IF @BuyAmount IS NULL SET @BuyAmount = 0

SELECT * 
FROM #Purchase
WHERE Total < 5000 AND Total > @BuyAmount
ORDER BY Date DESC

Upvotes: 2

Related Questions