Avoiding multiple `or` expressions
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
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