Skip to content

Check if any value in list satisfies a condition

Suppose I have the following table called Seasons:

start_month end_month
2 6
3 4

I need to write a query which, for a given list of months, returns all the Seasons that satisfy the condition where at least 1 month in the list is: start_month <= month <= end_month.

I’ve written this query as a native query with JDBC, except the where clause.

public class SeasonsRepositoryImpl implements SeasonsRepositoryCustom {

    private EntityManager em;

    public List<SeasonsProjection> findByMonths(List<Integer> months) {
        String query = "select * " +
                       "from seasons as s" +
                       "where ...."

        try {
            return  em.createNativeQuery(query)
                    .setParameter("months", months)
        } catch (Exception e) {
            log.error("Exception with an exception message: {}", e.getMessage());
            throw e;


I have no idea how to write this, I thought of using the ANY operator until I found out that ANY only works with tables and not lists, I thought of writing this as a subquery with converting the list to a table, but I don’t know if that’s possible, I couldn’t find anything in the MySQL documentation.



One way to accomplish this is:

select s.* 
from (select 1 as mn union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8
   union select 9 union select 10 union select 11 union select 12) as a
inner join season s on between s.start_month and s.end_month
where in (:flexibleTypeMonths);
User contributions licensed under: CC BY-SA
9 People found this is helpful