Search code examples
sqlselectinformixrelational-division

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

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.

Solution

  • 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)