SQL Or with a stop after first check

Question

Is it possible to have SQL stop checking the WHERE clause once a condition is met? For instance, if I have a statement as below:

SELECT * FROM Table1 
WHERE Table1.SubID = (SELECT TOP 1 SubID FROM Table2 ORDER BY Date DESC) 
OR Table1.OrderID = (SELECT TOP 1 OrderID FROM Table2 ORDER BY Date DESC)

Is it possible to stop execution after the first check? In essence, only one of the two checks in the where clause should be used, giving precedence to the first. Example cases below.

Example Cases:

Case1

Table1 SubID=600 OrderID=5

Table2 TOP 1 SubID=NULL

Table2 TOP 1 OrderID=5

Matches the OrderID to 5

Case 2

Table1 SubId=600 OrderId=5

Table2 Top 1 SubID=600

Table2 Top 1 OrderID=3

Matches to SubID=600, not OrderID=3

Given suggested answers, a with seems the best possible solution to solve what SQL is not inherently able to do. For my specific situation, the issue comes when attempting to put this into an outer apply, as below.

SELECT * FROM tbl_MainFields
OUTER APPLY
(
    WITH conditional AS
    (
        SELECT 1 AS 'choice', PlanCode, Carrier
        FROM tbl_payers
        WHERE tbl_payers.PlanCode = 
            (
                SELECT TOP 1 PlanCode 
                FROM tbl_payerDenials 
                WHERE tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
                ORDER BY InsertDate DESC
            )
        UNION ALL
        SELECT 2 AS 'choice', PlanCode, Carrier
        FROM tbl_payers
        WHERE tbl_payers.OrderNum = 
            (
                SELECT TOP 1 DenialLevel
                FROM tbl_payerDenials
                WHERE tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
                ORDER BY InsertDate DESC
            )
)
SELECT
    PlanCode AS DenialPC,
    Carrier AS DenialCAR
FROM conditional
WHERE choice = (SELECT MIN(choice) FROM conditional)    

) denialData

There is no guarantee that SQL server will preserve the order of the two statements, but I would suspect that it does short-circuit. See [this answer](http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated) for a better explanation. — Cᴏʀʏ, Sep 30 '13 at 12:44

Radu Gheorghiu · Answer 1 · 2013-09-30T13:42:37.200

4

I think you can try something like this

WITH conditional AS(

    SELECT 1 AS 'choice', PlanCode, Carrier
    FROM tbl_payers
    WHERE tbl_payers.PlanCode = 
        (
            SELECT TOP 1 PlanCode 
            FROM tbl_payerDenials
                JOIN tbl_mainFields ON 
                  tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
            ORDER BY InsertDate DESC
        )

    UNION ALL

    SELECT 2 AS 'choice', PlanCode, Carrier
    FROM tbl_payers
    WHERE tbl_payers.OrderNum = 
        (
            SELECT TOP 1 DenialLevel
            FROM tbl_payerDenials
                JOIN tbl_mainFields ON 
                  tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
            ORDER BY InsertDate DESC
        )
)
SELECT * FROM tbl_MainFields tMF
OUTER APPLY
(    
    SELECT * 
    FROM conditional c
    WHERE c.choice = (SELECT MIN(choice) FROM conditional)
) denialData

I'm using the 1 and 2 values to mark the queries, and then select the information from the first, if it returns values, otherwise it returns values from the second query (the MIN(choice) part).

I hope it is clear.

edited Sep 30 '13 at 13:42

answered Sep 30 '13 at 12:45

Radu Gheorghiu

20,049
16
72
107

Very clever indeed. However, I'm having issue sintegrating this into my query as it uses outer applies. Could you please look at the edits I've made to the OP and give me your suggestions? – steventnorris Sep 30 '13 at 13:09
@steventnorris Try modifying your query like this. – Radu Gheorghiu Sep 30 '13 at 13:27
I get an error due to tbl_mainFields.AccountNumber not being bound. I believe the with outside the outer apply does not allow access to the mainFields row that is being processed. – steventnorris Sep 30 '13 at 13:28
You have to add your `OUTER APPLY` condition in the `WHERE` clause of the last `SELECT` statement. Add `AND tbl_MainFields.column = conditional.column` – Radu Gheorghiu Sep 30 '13 at 13:32
The same error is occurring. I am unable to get the tbl_mainFields.AccountNumber in the with field, which means I am unable to get the TOP 1 for that accountnumber. – steventnorris Sep 30 '13 at 13:37
Just use JOINS to do that. Your `WHERE` condition is equivalent to `JOIN`ing the tables. I've modified my answer, try now. – Radu Gheorghiu Sep 30 '13 at 13:43
But if you're selecting top 1 in the with using a join, it will only get the top 1 for the top 1 accountnumber. I need this to be calculated per each accountnumber, so it changes for each account. I'm posting an answer that I came to because of what you suggested now. – steventnorris Sep 30 '13 at 13:45

score 0 · Answer 2 · answered Sep 30 '13 at 12:45

No, this cannot be done. The query optimizer, when sent a query, optimizes the entire query. Further, it creates a plan to leverage the entire query. If you need to do this you'll want to look at doing something like this:

SELECT *
INTO #tbl
FROM Table1
WHERE Table1.SubID = (SELECT TOP 1 SubID FROM Table2 ORDER BY Date DESC)

IF ( NOT EXISTS (SELECT * FROM #tbl) )
BEGIN
    SELECT *
    INTO #tbl
    FROM Table1
    WHERE Table1.OrderID = (SELECT TOP 1 OrderID FROM Table2 ORDER BY Date DESC)
END

score -1 · Accepted Answer · answered Sep 30 '13 at 13:46

Credit to @RaduGheorghiu for the inspiration. This functionality is similar to the WITH and MIN combination suggested, but allows for use within an OUTER APPLY

SELECT * FROM tbl_MainFields
OUTER APPLY
(
    SELECT TOP 1
        PlanCode AS DenialPC,
        Carrier AS DenialCAR,
        Precedence
    FROM
    (
        SELECT 
            1 AS 'Precedence', 
            PlanCode, 
            Carrier
        FROM tbl_payers
        WHERE tbl_payers.PlanCode = 
            (
                SELECT TOP 1 PlanCode 
                FROM tbl_payerDenials 
                WHERE tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
                ORDER BY InsertDate DESC
            )
        UNION ALL
        SELECT 
            2 AS 'Precedence', 
            PlanCode, 
            Carrier
        FROM tbl_payers
        WHERE tbl_payers.OrderNum = 
            (
                SELECT TOP 1 DenialLevel
                FROM tbl_payerDenials
                WHERE tbl_payerDenials.AccountNumber = tbl_mainFields.AccountNumber
                ORDER BY InsertDate DESC
            )
    ) AS denialPrecedence
    ORDER BY Precedence
) denialData

SQL Or with a stop after first check

3 Answers3