SQL query to match a list of values with a list of fields in any order without repetition

Question

I recently had to wrote a query to filter some specific data that looked like the following:

Let's suppose that I have 3 distinct values that I want to search in 3 different fields of one of my tables on my database, they must be searched in all possible orders without repetition.

Here is an example (to make it easy to understand, I will use named queries notation to show where the values must be placed):

val1 = "a", val2 = "b", val3 = "c"

This is the query I've generated:

SELECT * FROM table WHERE
(fieldA = :val1 AND fieldB = :val2 AND fieldC = :val3) OR
(fieldA = :val1 AND fieldB = :val3 AND fieldC = :val2) OR
(fieldA = :val2 AND fieldB = :val1 AND fieldC = :val3) OR
(fieldA = :val2 AND fieldB = :val3 AND fieldC = :val1) OR
(fieldA = :val3 AND fieldB = :val1 AND fieldC = :val2) OR
(fieldA = :val3 AND fieldB = :val2 AND fieldC = :val1)

What I had to do is generate a query that simulates a permutation without repetition. Is there a better way to do this type of query?

This is OK for 3x3 but if I need to do the same with something bigger like 9x9 then generating the query will be a huge mess.

I'm using MariaDB, but I'm okay accepting answers that can run on PostgreSQL. (I want to learn if there is a smart way of writing this type of queries without "brute force")

Answer 1

There isn't a much better way, but you can use in:

SELECT *
FROM table
WHERE :val1 in (fieldA, fieldB, fieldC) and
      :val2 in (fieldA, fieldB, fieldC) and
      :val3 in (fieldA, fieldB, fieldC)

It is shorter at least. And, this is standard SQL, so it should work in any database.

Answer 2

... I'm okay accepting answers that can run on PostgreSQL. (I want to learn if there is a smart way of writing this type of queries without "brute force")

There is a "smart way" in Postgres, with sorted arrays.

Integer

For integer values use sort_asc() of the additional module intarray.

SELECT * FROM tbl
WHERE  sort_asc(ARRAY[id1, id2, id3]) = '{1,2,3}' -- compare sorted arrays

Works for any number of elements.

Other types

As clarified in a comment, we are dealing with strings.
Create a variant of sort_asc() that works for any type that can be sorted:

CREATE OR REPLACE FUNCTION sort_asc(anyarray)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT array_agg(x ORDER BY x COLLATE "C") FROM unnest($1) AS x';

Not as fast as the sibling from intarray, but fast enough.

• Make it IMMUTABLE to allow its use in indexes.
• Use COLLATE "C" to ignore sorting rules of the current locale: faster, immutable.
• To make the function work for any type that can be sorted, use a polymorphic parameter.

Query is the same:

SELECT * FROM tbl
WHERE  sort_asc(ARRAY[val1, val2, val3]) = '{bar,baz,foo}';

Or, if you are not sure about the sort order in "C" locale ...

SELECT * FROM tbl
WHERE  sort_asc(ARRAY[val1, val2, val3]) = sort_asc('{bar,baz,foo}'::text[]);

Index

For best read performance create a functional index (at some cost to write performance):

CREATE INDEX tbl_arr_idx ON tbl (sort_asc(ARRAY[val1, val2, val3]));

SQL Fiddle demonstrating all.