All geek questions in one place

SQL or with a stop after the first check

Is it possible for SQL to stop checking the WHERE clause after the condition is met? For example, if I have an instruction as shown 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? Essentially, you should use only one of the two checks in the where clause, giving priority to the first. Examples are below.

Sample cases:

Case 1

 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 the suggested answers, with seems to be the best solution to solve what SQL inherently cannot do. For my specific situation, the problem occurs when trying to include this in outer apply , as shown 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
+6
source share
3 answers

Credit @RaduGheorghiu for inspiration. This functionality is similar to the proposed combination of WITH and MIN , but allows you to use it in 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
0
source

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 use values 1 and 2 for mark queries, and then select information from the first if it returns values, otherwise it returns values ​​from the second query ( MIN(choice) part).

I hope this is clear.

+3
source

No, It is Immpossible. The query optimizer optimizes the entire query when sending a request. In addition, he creates a plan to use the entire request. If you need to do this, you'll want to look at 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
0
source

Source: https://habr.com/ru/post/954939/

More articles:


All Articles