0

I have 2 Select Queries

select Amount,
       CurrentBalanceCurrency,
       AmountType
from UserBalance
where AmountType= 10

select Amount,
       CurrentBalanceCurrency,
       AmountType
from UserBalance
where AmountType= 20

I want to make this query in one line, without hitting my database twice.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Druvo
  • 13
  • 4
  • 2
    Did you try searching at all? I suggest looking up the syntax for [IN (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-2017). – Thom A Sep 03 '18 at 09:21
  • 2
    `AmountType IN (10,20)` is a shortcut for `(AmountType = 10 or AmountType = 20)` – dnoeth Sep 03 '18 at 09:22

4 Answers4

3

Use IN operator in where clause:

select Amount, CurrentBalanceCurrency, AmountType 
from UserBalance 
where AmountType in (10,20);
Ullas
  • 11,450
  • 4
  • 33
  • 50
Fahmi
  • 37,315
  • 5
  • 22
  • 31
2

You can use IN operator.

Query

select [Amount], [CurrentBalanceCurrency], [AmountType]
from [UserBalance]
where [AmountType] in (10,20);
Ullas
  • 11,450
  • 4
  • 33
  • 50
1

You should use OR between those two conditions:

select Amount
,CurrentBalanceCurrency
,AmountType 
from UserBalance where AmountType= 10 OR AmountType = 20
Petr Kalát
  • 11
  • 2
0

Another option is to use union all to concatenate the result-sets, this can improve performance over or or in in some cases (you would have to test for yourself):

select Amount,
       CurrentBalanceCurrency,
       AmountType
from UserBalance
where AmountType= 10
union all
select Amount,
       CurrentBalanceCurrency,
       AmountType
from UserBalance
where AmountType= 20
MJH
  • 1,710
  • 1
  • 9
  • 19
  • 1
    Can the person who downvoted this answer provide some feedback as to why? – MJH Sep 03 '18 at 09:26
  • I didn't down vote - I don't disagree with the down vote, although I would not have down voted it, as this answer will return the correct result. You have 2 selects instead of 1 which is unnecessary. The 2 selects will have a worse execution plan than the other answers for this. – t-clausen.dk Sep 03 '18 at 09:42
  • I am not down voter. But I have question. That, How many times this query hits the DB..? – Pugal Sep 03 '18 at 09:44
  • @IdontKnowEnglish this query hits the database once. Just different execution plan – t-clausen.dk Sep 03 '18 at 09:47
  • I did not have knowledge about the execution plan..But Okay.. question 2). `this can improve performance` on which basis...? I want try my self. – Pugal Sep 03 '18 at 09:51
  • If you have two **or** conditions in your where clause, sometimes the optimiser can choose to do a scan instead of a seek, using **union all** can cause the plan to use two seeks instead. That's why I used the words "Another option" in my answer, there are many ways to write a query like this. Ref: https://stackoverflow.com/questions/5639710/union-all-vs-or-condition-in-sql-server-query – MJH Sep 03 '18 at 09:54
  • @MJH, that link is hard to understand..Although I keep in mind. Thx – Pugal Sep 03 '18 at 10:07