Revisions to Avoiding multiple `or` expressions

added DBFiddle

Source Link

edited Feb 19, 2017 at 17:46

40.2k
15
104
177

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

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;

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

simplify the CTE

Source Link

edited Feb 19, 2017 at 13:10

ypercubeᵀᴹ

98.6k
13
215
305

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'    subject, '111' term FROM dual UNION ALL
      SELECT 'STAT'    subject, '222' term FROM dual UNION ALL
      SELECT 'ENGLISH' subject, '555' term FROM dual UNION ALL
      SELECT 'COMM'    subject, '444' term FROM dual UNION ALL
      SELECT 'COMM'    subject, '333' term FROM dual UNION ALL
      SELECT 'STAT'    subject, '666' term FROM dual )
SELECT * 
  FROM foobar             fb
 INNER JOIN subject_terms st
    ON fb.subject = st.subject
   AND fb.term    = st.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 AS
    ( SELECT 'STAT'    subject, '111' term FROM dual UNION ALL
      SELECT 'STAT'    subject, '222' term FROM dual UNION ALL
      SELECT 'ENGLISH' subject, '555' term FROM dual UNION ALL
      SELECT 'COMM'    subject, '444' term FROM dual UNION ALL
      SELECT 'COMM'    subject, '333' term FROM dual UNION ALL
      SELECT 'STAT'    subject, '666' term FROM dual )
SELECT * 
  FROM foobar             fb
 INNER JOIN subject_terms st
    ON fb.subject = st.subject
   AND fb.term    = st.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;

Source Link

created Jan 2, 2015 at 15:06

Gandolf989

1.5k
1
13
18

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 AS
    ( SELECT 'STAT'    subject, '111' term FROM dual UNION ALL
      SELECT 'STAT'    subject, '222' term FROM dual UNION ALL
      SELECT 'ENGLISH' subject, '555' term FROM dual UNION ALL
      SELECT 'COMM'    subject, '444' term FROM dual UNION ALL
      SELECT 'COMM'    subject, '333' term FROM dual UNION ALL
      SELECT 'STAT'    subject, '666' term FROM dual )
SELECT * 
  FROM foobar             fb
 INNER JOIN subject_terms st
    ON fb.subject = st.subject
   AND fb.term    = st.term;

Stack Exchange Network

Return to Answer