SQL: Is there a better way to check if a list of values is in a list of fields?

Joe U 0
Viewed 34

I want to see if the values 'PS_BA' OR 'PS_BS' are in a list of fields:

Here is how I accomplished it with a bunch of OR statements.

Is there a better/more efficient way to do this (especially when the lists are much longer)?

SELECT * 
FROM TABLE
WHERE
     (  ACAD_PLAN_CD   in ('PS_BS','PS_BA')
     OR ACAD_PLAN_CD_2 in ('PS_BS','PS_BA')
     OR ACAD_PLAN_CD_3 in ('PS_BS','PS_BA')
     OR ACAD_PLAN_CD_4 in ('PS_BS','PS_BA')
     OR ACAD_PLAN_CD_5 in ('PS_BS','PS_BA'))
oracle-sqldeveloper sql
1 Answers

You could rephrase your query as:

SELECT *
FROM yourTable
WHERE 'PS_BS' IN (ACAD_PLAN_CD, ACAD_PLAN_CD_2, ACAD_PLAN_CD_3,
                  ACAD_PLAN_CD_4, ACAD_PLAN_CD_5) OR
      'PS_BA' IN (ACAD_PLAN_CD, ACAD_PLAN_CD_2, ACAD_PLAN_CD_3,
                  ACAD_PLAN_CD_4, ACAD_PLAN_CD_5);
Tim Biegeleisen0
Related