SQL query always returns a result set. Each result set has 0-N rows with same columns. When we write an SQL expression, we manipulate columns and filter (or combine) rows.

Let's look on the following code:

FluentQuery query = FluentJPA.SQL(() -> {

    ProductModel productModel = subQuery((ProductModel model) -> {
        SELECT(model);
        FROM(model);
    });
});

It's clear that subQuery() above may return multiple rows, but we cast the result to a single ProductModel "instance". This is required to be able to access result set columns in the same way as we would do it in native SQL. Had we wrote this:

FluentQuery query = FluentJPA.SQL(() -> {

    ProductModel productModel = subQuery((ProductModel model) -> {
        SELECT(model);
        FROM(model);
    });

    // SELECT/FROM a single instance!
    SELECT(productModel);
    FROM(productModel);
});

//and then a List is returned
List<ProductModel> productModels = query.
                                   createQuery(em, ProductModel.class).getResultList();

Note, we SELECT() from an instance, but the final result is a List<>.

Conclusion: inside a query we use a single entity to represent the entire result set.
It's important to say that this approach is invented by JPA and JPQL works this way.

When we decide to not map a Table as @Entity, or a DTO projection is returned, we need a new type. In most case it will be annotated as @Tuple, see entities & tuples for best practices and considerations when declaring new types.

Note, DTO projection can be also a result of a sub query. We may need a new type for them!

In case the result is returned to Java, FluentJPA always performs the mapping, keeping the same experience for @Tuple and @Entity annotated types.

Let's recall the IN operator in SQL:

<column> IN (SELECT <single column> FROM ...)

We just argued that SELECT ... FROM ... maps to an entity type. To be used in IN, the SELECT above must select only a single column. In other words we need a projection of that one column from the entity. And in Java this projection maps very well to Collection type. FluentJPA offers a ready to use function to create this projection and maps Collection.contains() to IN. At the end we should be able get the following:

collect(productModel, productModel.getType()).contains(...);

See step-by-step guide for this technique.

In SQL, if the returned result set has just one column and one row, in scalar context it implicitly converts to a scalar. Like collect, returning projection as a Collection, FluentJPA offers pick, returning projection as a scalar. So we can write code like this:

FluentJPA.SQL((Film film) -> {

    SELECT(film);
    FROM(film);
    WHERE(film.getRentalRate() > pick(film, AVG(film.getRentalRate())));
});

See step-by-step guide for this technique.