July 6, 2021 at 11:19 pm
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: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
July 7, 2021 at 2:58 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 8, 2021 at 4:35 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply