SQL Query similar to IN where clause with AND condition instead of OR

Question

I am trying to optimize my SQL query, so that we will not have to process the response on our JVM.

Consider we have following table with entries:

+-----------+-------------+
| Column1   | Column2     |
+-----------+-------------+
|val11      |val21        |
|val11      |val22        |
|val11      |val23        |
|val12      |val21        |
|val12      |val24        |
+-----------+-------------+

Now, I want execute a query which will result me column1s having rows mapped to Column2s values val21, val22, val23.

Something similar to IN where clause, but, as IN where clause searches for data with OR between the values of IN clause, I want to search for AND in between these values.

For IN where clause:

SELECT Column1 from table
WHERE Column2 IN (val21, val22, val23)

will result in both val11 and val12 (as IN clause will check for data with val21, or val22 or val23).

Instead I want to have some query which will check Column1 having mapping with all three val21, val22, val23 as we have for val11.

Using Informix DB.

Answer 1

This is called "relational division".

The usual approach for this, is something like the following:

select column1
from x
where column2 in ('val21', 'val22', 'val23')
group by column1
having count(distinct column2) = 3;

Note that this would also include values that have more then those three values assigned in column2 (so it returns those that have at least those three values)

Answer 2

You can also do this but please note that it will return the cases where column2 has less records than the array following IN. With this technique you make sure that all the values from column2 have a match in the array, for the given column1 group. Make sure you handle NULL values appropriately.

SELECT column1
FROM t
WHERE t.column2  IN (11,21)
AND NOT EXISTS (SELECT 1
        FROM t t1
        WHERE  t1.column2  NOT IN (11,21)
        AND t1.column1 = t.column1)