Subqueries

Typed Subqueries

IN expression based on single column

Java

BookTable book = Tables.BOOK_TABLE;
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(book)
    .where(
        book.id().in(sqlClient
            .createSubQuery(author) 
            .where(author.firstName().eq("Alex"))
            .select(author.books().id())
        )
    )
    .select(book)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            table.id valueIn subQuery(Author::class) {
                where(table.firstName eq "Alex")
                select(table.books.id) 
            }
        )
        select(table)    
    }
    .execute()

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE, 
    tb_1_.STORE_ID
from BOOK as tb_1_
where
    /* highlight-next-line */  
    tb_1_.ID in (
        select
            tb_3_.BOOK_ID
        from AUTHOR as tb_2_
        inner join BOOK_AUTHOR_MAPPING as tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
            tb_2_.FIRST_NAME = ?  
    )

IN expression based on multiple columns

Java

BookTable book = Tables.BOOK_TABLE;

List<Book> newestBooks = sqlClient
    .createQuery(book)
    .where(
        Expression.tuple(
            book.name(),
            book.edition()
        ).in(sqlClient
            .createSubQuery(book)
            .groupBy(book.name())
            .select(
                book.name(),
                book.edition().max() 
            )
        )
    )
    .select(book)
    .execute();

Kotlin

val newestBooks = sqlClient
    .createQuery(Book::class) {
        where(
            tuple(
                table.name,
                table.edition  
            ) valueIn subQuery(Book::class) {
                groupBy(table.name)
                select(
                    table.name, 
                    max(table.edition).asNonNull()
                )
            }
        )
        select(table)
    }
    .execute()

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_  
where
    /* highlight-next-line */
    (tb_1_.NAME, tb_1_.EDITION) in (
        select
            tb_2_.NAME,
            max(tb_2_.EDITION)
        from BOOK as tb_2_
        group by tb_2_.NAME 
    )

Treat subquery as simple value

Java

BookTable book = Tables.BOOK_TABLE;

List<Book> newestBooks = sqlClient
    .createQuery(book)
    .where(
        book.price().gt(sqlClient
            .createSubQuery(book)
            .groupBy(book.name())
            .select(
                book
                    .price()
                    .avg()
                    .coalesce(BigDecimal.ZERO)
            )
        )
    )
    .select(book)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            table.price gt subQuery(Book::class) {
                select(
                    avg(table.price)
                        .coalesce(BigDecimal.ZERO)
                )
            }
        )
        select(table)
    }
    .execute() 

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION, 
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_   
where
    /* highlight-next-line */
    tb_1_.PRICE > (
        select
            coalesce(avg(tb_2_.PRICE), ?)
        from BOOK as tb_2_  
    )

Use subquery in select and orderBy clauses

Java

BookStoreTable store = Tables.BOOK_STORE_TABLE;
BookTable book = Tables.BOOK_TABLE;

MutableRootQuery<BookStoreTable> query = 
    sqlClient.createQuery(store);
TypedSubQuery<BigDecimal> subQuery =
    sqlClient
        .createSubQuery(book) 
        .where(book.store().eq(store))
        .select(
            book
                .price()
                .avg()
                .coalesce(BigDecimal.ZERO)  
        );
List<Tuple2<BookStore, BigDecimal>> storeAvgPriceTuples =
    query
        
        .orderBy(
            subQuery.desc() 
        )
        .select(
            store,
            subQuery
        )
        .execute();

Kotlin

val storeAvgPriceTuples = sqlClient
    .createQuery(BookStore::class) {
        val avgPriceSubQuery = subQuery(Book::class) {
            where(table.store eq parentTable)
            select(avg(table.price))
        }
        
        orderBy(
            avgPriceSubQuery.desc()
        )
        select(
            table,
            avgPriceSubQuery
        )
    }
    .execute()

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.WEBSITE,
    (
        /* highlight-next-line */  
        select coalesce(avg(tb_2_.PRICE), ?)
        from BOOK as tb_2_
    )
from BOOK_STORE as tb_1_
order by (  
    /* highlight-next-line */
    select coalesce(avg(tb_2_.PRICE), ?)
    from BOOK as tb_2_ 
) desc

Use ANY operator

Java

BookTable book = Tables.BOOK_TABLE;
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(book)
    .where(
        book.id().eq(sqlClient
            .createSubQuery(author)
            .where(
                author.firstName().in(
                    Arrays.asList("Alex", "Bill")
                )
            )
            .select(author.books().id())
            .any()
        )  
    )
    .select(book)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            table.id eq any(
                subQuery(Author::class) {
                    where(
                        table.firstName valueIn listOf(
                            "Alex",
                            "Bill"
                        )  
                    )
                    select(table.id)
                }
            )
        )
        select(table)
    }
    .execute()

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_ 
where tb_1_.ID =
    /* highlight-next-line */ 
    any(
        select
            tb_3_.BOOK_ID
        from AUTHOR as tb_2_
        inner join BOOK_AUTHOR_MAPPING as tb_3_ 
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
            tb_2_.FIRST_NAME in (?, ?) 
    )

Use ALL operator

Java

BookTable book = Tables.BOOK_TABLE;
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(book) 
    .where(
        book.id().ne(sqlClient
            .createSubQuery(author)
            .where(
                author.firstName().in(  
                    Arrays.asList("Alex", "Bill")
                )
            )
            .select(author.books().id())
            .all()
        )
    )
    .select(book)
    .execute(); 

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            table.id ne all(
                subQuery(Author::class) {
                    where(
                        table.firstName valueIn listOf(
                            "Alex",
                            "Bill"  
                        )
                    )
                    select(table.id)
                }
            )
        )
        select(table)
    }
    .execute()

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_  
where tb_1_.ID =
    /* highlight-next-line */
    all(
        select
            tb_3_.BOOK_ID
        from AUTHOR as tb_2_
        inner join BOOK_AUTHOR_MAPPING as tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
            tb_2_.FIRST_NAME in (?, ?) 
    )

Use EXISTS operator

Java

BookTable book = Tables.BOOK_TABLE;
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(book)
    .where(sqlClient
        .createSubQuery(author)
        .where(
            author.books().eq(book),
            author.firstName().eq("Alex") 
        )
        .select(author)
        .exists()
    )
    .select(book)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            exists(
                subQuery(Author::class) {
                    where(
                        table.books eq parentTable,
                        table.firstName eq "Alex"
                    )  
                    select(table)
                }
            )
        )
        select(table)
    }
    .execute() 

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_   
where
    /* highlight-next-line */
    exists (
        select
            1
        from AUTHOR as tb_2_
        inner join BOOK_AUTHOR_MAPPING as tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
            tb_1_.ID = tb_3_.BOOK_ID
        and
            tb_2_.FIRST_NAME = ?  
    )

info

Note that in the final SQL, the selected column in the subquery is the constant 1, not what is specified in the Java/Kotlin code.

This is because the exists operator only cares if the subquery can match data, not what columns are selected. Whatever you select in the Java/Kotlin code will be ignored.

Untyped Subqueries

The last example in previous section is exists subquery where whatever is selected in Java code gets ignored.

In that case, why specify the return type for exists subqueries in Java code?

Therefore, jimmer-sql supports untyped subqueries (wild subqueries). Unlike normal subqueries, for wild subqueries the final select() call is no longer needed, i.e. no return type.

Java

BookTable book = Tables.BOOK_TABLE; 
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(book)
    .where(sqlClient
        .createSubQuery(author)
        .where(
            author.books().eq(book),
            author.firstName().eq("Alex")
        )
        // No select here  
        .exists()
    )
    .select(book)
    .execute();

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        where(
            exists(
                wildSubQuery(Author::class) {
                    where(
                        table.books eq parentTable, 
                        table.firstName eq "Alex"
                    )
                    // No select here
                }
            ) 
        )
        select(table)
    }
    .execute()  

The final SQL remains the same:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK as tb_1_    
where
    /* highlight-next-line */ 
    exists (
        select
            1
        from AUTHOR as tb_2_
        inner join BOOK_AUTHOR_MAPPING as tb_3_
            on tb_2_.ID = tb_3_.AUTHOR_ID
        where
            tb_1_.ID = tb_3_.BOOK_ID
        and
            tb_2_.FIRST_NAME = ?   
    )

info

The only value of untyped subqueries is when used with exists operator.