Conditionally Returning Rows Based On Query Results

Watch this week's video on YouTube

While I normally prefer formatting my query results in a downstream app/reporting layer, sometimes I can't get around adding some business formatting logic to my SQL queries.

Today I want to show you four different ways to conditionally output rows of data based on a SQL query.

Setting Up The Base Query

You've probably worked with a query that results in either 0 or 1 rows being returned:

DECLARE @CurrentDatetime datetime = getdate();
SELECT
    1 AS AreSecondsDivisbleBy2,
    @CurrentDatetime AS CurrentDatetime
WHERE
    DATEPART(second,@CurrentDatetime) % 2 = 0

If you put that query into a derived table and add some IIF() logic on top of it, you now have a situation where you your result may contain a row with one of two distinct values or no rows at all:

If you put that query into a derived table and add some IIF() logic on top of it, you now have a situation where you your result may contain a row with one of two distinct values or no rows at all:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT
    IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisbleBy3And2,
    d.CurrentDatetime
FROM
    (
    SELECT
        1 AS AreSecondsDivisbleBy2,
        @CurrentDatetime AS CurrentDatetime
    WHERE
        DATEPART(second,@CurrentDatetime) % 2 = 0
    ) d

(Note: if you ever need to know whether the seconds part of the current time is divisible by 3 and 2, use SELECT IIF(DATEPART(second,GETDATE()) % 6 = 0,1,0) and not this monstrosity of a query  I'm creating to demonstrate a scenario when you dependent derived table logic).

Sometimes we may want to force certain scenarios based on the output of the query above. Here are few common patterns that I find myself doing when needing to meet certain business requirements of queries to return data conditionally.

Always Return 1 Row

Let's say we are happy with getting a result value of 0 or 1 for AreSecondDivisbleBy3And2, but want to additionally return some other value when our derived table returns no rows. We can accomplish this with a UNION ALL and some sorting:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT TOP 1
    AreSecondsDivisbleBy3And2,
    CurrentDatetime
FROM
    (
    SELECT
        IIF(DATEPART(second,CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisbleBy3And2,
        d.CurrentDatetime,
        0 AS OrderPrecedence
    FROM
        (
        SELECT
            1 AS AreSecondsDivisbleBy2,
            @CurrentDatetime AS CurrentDatetime
        WHERE
            DATEPART(second,@CurrentDatetime) % 2 = 0
    ) d
    UNION ALL
    SELECT
        -1 AS AreSecondsDivisbleBy3And2,
        @CurrentDatetime AS CurrentDatetime,
        1 AS OrderPrecedence
    ) p
ORDER BY
     OrderPrecedence

We can limit our query to return TOP 1 and then add an OrderPrecedence column to determine which query result row to return. If our original query has a row of data, it will be returned because of it's OrderPrecedence. If our original query returns 0 rows, our fall back UNION ALL default value of -1 will be returned.

Return 1 row when value is 1, 0 rows when value is 0

What about a situation where we want to return a row when AreSecondsDivisbleBy3And2 is equal to 1, but no row when it is equal to 0?

The IIF function and CASE statements work great for conditionally returning a value in a SELECT, but how can we conditionally return a row?  Here's one approach:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT
     AreSecondsDivisibleBy3And2,
    CurrentDatetime
FROM
    (
    SELECT
        IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2,
        CurrentDatetime
    FROM
        (
        SELECT
            1 AS AreSecondsDivisbleBy2,
            @CurrentDatetime AS CurrentDatetime
        WHERE
            DATEPART(second,@CurrentDatetime) % 2 = 0
        ) d
    ) d1
    INNER JOIN
    (
    SELECT
        0 AS ValueToNotReturn
    ) d2
        ON d1.AreSecondsDivisibleBy3And2 != d2.ValueToNotReturn

In this scenario, we return a row when our value is 1, but do not return a row when the value is 0 or our derived table doesn't return any rows.

We use an INNER JOIN to filter out the row value that we want to return 0 rows.

Return 1 row when null, and 0 rows when the value is 1 or 0

In this scenario we want to return no rows when AreSecondsDivisibleBy3And2=1 and a row when its not.

I've never had a real-world use for this one, but it's essentially a combination of the first two solutions.

SELECT
    AreSecondsDivisibleBy3And2,
    CurrentDatetime
FROM
    (
    SELECT TOP 1
        AreSecondsDivisibleBy3And2,
        CurrentDatetime
    FROM
        (
        SELECT
            IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2,
            d.CurrentDateTime,
            0 AS OrderPrecedence
        FROM
            (
            SELECT
                1 AS AreSecondsDivisbleBy2,
                @CurrentDatetime AS CurrentDatetime
            WHERE
                DATEPART(second,@CurrentDatetime) % 2 = 0
            ) d
        UNION ALL
        SELECT
            -1 AS AreSecondsDivisibleBy3And2,
            @CurrentDatetime AS CurrentDatetime,
            1 AS OrderPrecedence
        )u
        ORDER BY
             OrderPrecedence
    ) d1
    INNER JOIN
    (
    SELECT
        -1 AS ValueToNotReturn
    ) d2
        ON d1.AreSecondsDivisibleBy3And2 = d2.ValueToNotReturn

This one is by far the most difficult one to logic through, but it's a pure reversal of return no rows when rows are present and return a row when no rows are present.

Always return 0 rows

I'm not exactly sure what the business case for never returning any rows would be, but this one is pretty simple: just add a condition that will always evaluate to false:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT
    IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2
FROM
    (
    SELECT
        1 AS AreSecondsDivisbleBy2,
        @CurrentDatetime AS CurrentDatetime
    WHERE
        DATEPART(second,@CurrentDatetime) % 2 = 0
    ) d
WHERE
    1=0

Since 1=0 will never be true, your query will never return any results regardless of what kind of logic is happening in your SELECT or derived table.