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')
  ...
up vote 20 down vote accepted

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

  • 2
    I wish MS SQL Server had this syntax. – Ross Presser Jan 7 '15 at 5:34
  • @RossPresser I think there is a Connect item/suggestion for the syntax to be added. You can vote ;) – ypercubeᵀᴹ Feb 17 '17 at 10:04
  • I found that there is something roughly as capable: SELECT * FROM foobar INNER JOIN (SELECT * FROM (VALUES ('4','a'),('5','b')) AS myTable(subject,term)) ON myTable.subject=foobar.table and mytable.term=foobar.term – Ross Presser Feb 19 '17 at 5:28
  • but I would like this actual syntax. Any idea where the Connect item is? – Ross Presser Feb 19 '17 at 5:29
  • @RossPresser here it is: Add support for ANSI standard row value constructors The answer from MS was: "Hello. Thank you for your feedback. We're certainly considering row value constructors for a future release of SQL Server." At least the request is still open, 10 years now. – ypercubeᵀᴹ Feb 19 '17 at 12:25

In terms of a pure code cleansing, the following looks cleaner:

SELECT * 
  FROM foobar 
  WHERE (SUBJECT = 'STAT' and TERM IN ('111','222','666') )
    OR  (SUBJECT = 'COMM' and TERM IN ('333','444') )
    OR  (SUBJECT = 'ENGLISH' and TERM = '555' ) ;

Depending on the application and how often the logic will be reused, it may also be worth setting up a lookup table to apply the logic:

CREATE TABLE foobar_lookup (SUBJECT VARCHAR2(7), TERM VARCHAR2(3)) ;

INSERT INTO foobar_lookup SELECT 'STAT',    '111' FROM dual ;
INSERT INTO foobar_lookup SELECT 'STAT',    '222' FROM dual ;
INSERT INTO foobar_lookup SELECT 'STAT',    '666' FROM dual ;
INSERT INTO foobar_lookup SELECT 'COMM',    '444' FROM dual ;
INSERT INTO foobar_lookup SELECT 'COMM',    '333' FROM dual ;
INSERT INTO foobar_lookup SELECT 'ENGLISH', '555' FROM dual ;

SELECT f.* FROM foobar f
JOIN foobar_lookup fl 
    ON fl.subject = f.subject
    AND fl.term = f.term ;

Here is another way to do it. Using the where (col1,col2) might cause Oracle to not use any indexes, but this looks like a table to the query, so it might work better. You will know once you test the various versions.

  WITH subject_terms 
            (subject,   term) AS
    ( SELECT 'STAT'   , '111' FROM dual UNION ALL
      SELECT 'STAT'   , '222' FROM dual UNION ALL
      SELECT 'ENGLISH', '555' FROM dual UNION ALL
      SELECT 'COMM'   , '444' FROM dual UNION ALL
      SELECT 'COMM'   , '333' FROM dual UNION ALL
      SELECT 'STAT'   , '666' FROM dual )
SELECT * 
  FROM foobar             fb
 INNER JOIN subject_terms st
    ON fb.subject = st.subject
   AND fb.term    = st.term;

DBFiddle Here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.