SQL Query is like IN where where the condition is AND instead of OR
I am trying to optimize my SQL query so that we don't have to process the response in our JVM.
Consider the following table with records:
+-----------+-------------+
| Column1 | Column2 |
+-----------+-------------+
|val11 |val21 |
|val11 |val22 |
|val11 |val23 |
|val12 |val21 |
|val12 |val24 |
+-----------+-------------+
Now I want to execute a query that will cause column1s to have rows mapped to Column2s val21, val22, val23 values.
Something similar to the IN where clause, but since the IN where clause is looking for data with an OR between the values of the IN clause, I want to search for ANDs between those values.
For IN where clause:
SELECT Column1 from table
WHERE Column2 IN (val21, val22, val23)
will result in both val11 and val12 (as the IN clause will validate data with val21 or val22 or val23).
Instead, I want to get some query that will check for Column1 having a mapping against all three val21, val22, val23, just like for val11.
Using Informix DB.
source to share
This is called "relational division".
The usual approach for this is as follows:
select column1
from x
where column2 in ('val21', 'val22', 'val23')
group by column1
having count(distinct column2) = 3;
Note that this will also include values that have more than three values assigned to column2
(so it returns those that have at least those three values)
source to share
You can also do this, but note that it will return cases where column2 has fewer entries than the array following IN. With this method, you will ensure that all the values from column2 have a match in the array for the given column group1. Make sure you handle NULL values correctly.
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)
source to share