HiveBrain v1.2.0
Get Started
← Back to all entries
patternMajor

Avoiding multiple `or` expressions

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
avoidingexpressionsmultiple

Problem

I have the following oracle SQL and its works and all but it's quite ugly with all of the ors. Is there a more concise way of doing this?

SELECT * FROM foobar WHERE
  (SUBJECT ='STAT' and TERM ='111') or  
  (SUBJECT ='STAT' and TERM ='222') or  
  (SUBJECT ='ENGLISH' and TERM ='555') or 
  (SUBJECT ='COMM' and TERM ='444') or
  (SUBJECT ='COMM' and TERM ='333') or  
  (SUBJECT ='STAT' and TERM ='666')
  ...

Solution

You might prefer something like this:

select *
from foobar
where (subject,term) in ( ('STAT','111')
                         ,('STAT','222')
                         ,('ENGLISH','555')
                         ,('COMM','444')
                         ,('COMM','333')
                         ,('STAT','222')
                         ,('STAT','666') 
                        );


DBFiddle here

Code Snippets

select *
from foobar
where (subject,term) in ( ('STAT','111')
                         ,('STAT','222')
                         ,('ENGLISH','555')
                         ,('COMM','444')
                         ,('COMM','333')
                         ,('STAT','222')
                         ,('STAT','666') 
                        );

Context

StackExchange Database Administrators Q#87355, answer score: 21

Revisions (0)

No revisions yet.