Share via

SQL AND statement, avoid using between in the statement

Rock Hitman 46 Reputation points
2020-10-13T07:12:50.093+00:00

Hi, is there a way I can avoid using between in SQL statement and replace that with AND statement ?

Below is my query 

Select * from [dbo].[TestData] Where [ID] between (('PINNum'*10) + 1) AND  ('PINNum'+1)*10'

What I am trying to get : I want to achieve the above logic through AND statement . Please advice how can I acheive this ? 

Select * from [dbo].[TestData] Where [ID] = ((@PINNum*12) + 1)
AND 
(Select * from [dbo].[TestData] Where [ID] =  (@PINNum+1)*12)
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 126.8K Reputation points
    2020-10-13T07:56:35.5+00:00

    In certain circumstances, without AND, in a single statement: 

    select * from [dbo].[TestData] Where [ID] / 10 = @PINNum

    Here @PINNum means the slot or page to be extracted (0 for ID between 1 and 9, 1 for ID between 10 and 19, etc.). It depends on unclear details and relies on some previous questions.

    0 comments
  2. Viorel 126.8K Reputation points
    2020-10-13T07:35:44.993+00:00

    Maybe something like this, but without AND: 

    select * from [dbo].[TestData] Where [ID] >= @PINNum * 10
except
Select * from [dbo].[TestData] Where [ID] >  @PINNum * 10 + 10
    0 comments
  3. MelissaMa-msft 24,246 Reputation points Moderator
    2020-10-13T07:33:49.697+00:00

    Hi @Rock Hitman ,

    Please refer below and check whether it is helpful: 

    Select * from [dbo].TestData Where [ID] >= IIF(((@PINNum*12) + 1)>((@PINNum+1)*12), (@PINNum+1)*12, ((@PINNum*12) + 1))  
INTERSECT   
Select * from [dbo].TestData Where [ID] <= IIF(((@PINNum*12) + 1)<((@PINNum+1)*12), (@PINNum+1)*12, ((@PINNum*12) + 1))

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  4. Olaf Helper 47,616 Reputation points
    2020-10-13T07:18:56.273+00:00

    You can replace a BETWEEN with 2 greater/smaller equal compare, but from performance view/query execution nothing will change and in my option BETWEEN is better readable. 

    Select * 
from [dbo].[TestData] 
Where [ID] >= (('PINNum'*10) + 1) AND [ID] <= ('PINNum'+1) * 10
  5. MelissaMa-msft 24,246 Reputation points Moderator
    2020-10-13T07:17:53.407+00:00

    Hi @Rock Hitman ,

    You could use id>= low and id<=high instead of using between low and high.

    Please try with below: 

    Select * from [dbo].[TestData] Where [ID] >= ((@PINNum*12) + 1)  
 AND [ID] <=  (@PINNum+1)*12

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.