Comparing two sets of value sets

• Aaron N. Cutshall

  SSCrazy Eights

  Points: 8992

  More actions

  July 6, 2021 at 11:19 pm

  #3904186

  I have two columns that I need to check for a given set of values:

  SELECT *
  FROM test t
  WHERE t.col_a IN ('a','b','d','g','t','s')
    OR  t.col_b IN ('a','b','d','g','t','s')

  What I'm wondering is if there is a shorthand method to determine if either col_a or col_b have a value in the set without repeating the set of values. Something conceptually like this:

  SELECT *
  FROM test t
  WHERE (t.col_a, t.col_b) IN ('a','b','d','g','t','s')

  My only thought was to do something like this:

  SELECT *
  FROM test t
  WHERE EXISTS (SELECT 1
                FROM (VALUES ('a','b','d','g','t','s')) x(val)
                WHERE x.val IN (t.col_a, t.col_b)
                )

  Any better suggestions out there?

  LinkedIn: http://www.linkedin.com/in/sqlrv
  Twitter: http://www.twitter.com/sqlrv
  Website: http://sqlrv.com

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  July 7, 2021 at 2:58 pm

  #3905385

  If there are potential index(es) on col_a and/or col_b, then I'd stick with the first style.  If not, the second is ok too.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Eric M Russell

  SSC Guru

  Points: 125522

  More actions

  July 8, 2021 at 4:35 pm

  #3905791

  One option is to create a table or view that contains the set of values, and then inner join on col_1 and/or col_b.

  This would abstract the set of values from code and also make the set reusable.

  SELECT t.*
  FROM test AS t
  JOIN MyValues AS v
     ON v.value = t.col_a 
     OR v.value = t.col_b;

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho