easy-query: The Most Powerful ORM Subquery for Java

easy-query draws inspiration from many C# ORM frameworks (such as EF Core, FreeSql). In such a mature field, we don't claim to have revolutionary innovations. But when it comes to subqueries, easy-query is truly powerful — this is our most confident feature.

---

Why Are Subqueries So Important?

In real business development, subqueries are everywhere:

• Query "users who have orders"
• Query "authors with more than 10 articles"
• Query "members who have made purchases in the last 30 days"
• Count "each user's orders, comments, and favorites"

Traditional ORMs either don't support this, require raw SQL, or generate poorly performing SQL.

easy-query's goal is: Make subqueries as simple as regular queries while generating high-performance SQL.

---

1. Implicit Subqueries: Write Code Like Speaking

1.1 Existence Check: any / none

// Query users who have posts
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().any())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE EXISTS (SELECT 1 FROM t_post t1 WHERE t1.user_id = t.id)

// Query users who have no posts
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().none())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE NOT EXISTS (SELECT 1 FROM t_post t1 WHERE t1.user_id = t.id)

1.2 Conditional Existence Check

// Query users who have published posts
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().where(p -> p.status().eq(1)).any())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE EXISTS (
//     SELECT 1 FROM t_post t1 
//     WHERE t1.user_id = t.id AND t1.status = 1
// )

1.3 Universal Quantifier: all / notEmptyAll

// Query users whose all bank cards are savings cards with code starting with "622"
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.bankCards()
        .where(bc -> bc.type().eq("savings"))
        .all(bc -> bc.code().startsWith("622")))
    .toList();

// Generated SQL (uses NOT EXISTS + NOT logic):
// SELECT * FROM t_user t
// WHERE NOT EXISTS (
//     SELECT 1 FROM t_bank_card t1 
//     WHERE t1.user_id = t.id AND t1.type = 'savings' 
//     AND NOT (t1.code LIKE '622%')
//     LIMIT 1
// )

Difference between all and notEmptyAll:

Method	Empty collection	All elements match	Some elements don't match
all	✅ Pass	✅ Pass	❌ Fail
notEmptyAll	❌ Fail	✅ Pass	❌ Fail

// notEmptyAll: At least one savings card exists, and all savings cards start with "622"
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.bankCards()
        .where(bc -> bc.type().eq("savings"))
        .notEmptyAll(bc -> bc.code().startsWith("622")))
    .toList();

// notEmptyAll = any() + all(), meaning: exists AND all match

1.4 Aggregate Subqueries: count / sum / avg / max / min

// Query users with more than 5 posts
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().count().gt(5L))
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE (SELECT COUNT(*) FROM t_post t1 WHERE t1.user_id = t.id) > 5

// Query users with total order amount over 10000
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.orders().sum(o -> o.amount()).gt(new BigDecimal("10000")))
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// WHERE (SELECT SUM(amount) FROM t_order t1 WHERE t1.user_id = t.id) > 10000

1.5 Subqueries in ORDER BY

// Order by post count
List<User> users = easyEntityQuery.queryable(User.class)
    .orderBy(u -> u.posts().count().desc())
    .toList();

// Generated SQL:
// SELECT * FROM t_user t
// ORDER BY (SELECT COUNT(*) FROM t_post t1 WHERE t1.user_id = t.id) DESC

1.6 Subqueries in SELECT

// Query users with their post count
List<UserDTO> users = easyEntityQuery.queryable(User.class)
    .select(u -> new UserDTOProxy()
        .id().set(u.id())
        .username().set(u.username())
        .postCount().set(u.posts().count())
    )
    .toList();

// Generated SQL:
// SELECT t.id, t.username, 
//        (SELECT COUNT(*) FROM t_post t1 WHERE t1.user_id = t.id) AS post_count
// FROM t_user t

---

2. Subquery Merge Optimization: From N Scans to 1 Scan

This is the most powerful feature of easy-query's subquery handling.

2.1 The Problem: Performance Disaster with Multiple Subqueries

Suppose we want to query each user's post count and comment count:

// Standard approach
List<UserDTO> users = easyEntityQuery.queryable(User.class)
    .select(u -> new UserDTOProxy()
        .id().set(u.id())
        .postCount().set(u.posts().count())
        .commentCount().set(u.comments().count())
    )
    .toList();

SQL generated by typical ORMs:

SELECT t.id,
       (SELECT COUNT(*) FROM t_post WHERE user_id = t.id) AS post_count,
       (SELECT COUNT(*) FROM t_comment WHERE user_id = t.id) AS comment_count
FROM t_user t

Problem: With 1 million users, 5 million posts, and 10 million comments, each user requires two subqueries — that's 2 million scans of the child tables.

2.2 easy-query's Optimization: Subquery to GROUP JOIN

easy-query automatically merges multiple subqueries into GROUP JOINs:

SELECT t.id,
       IFNULL(t1.post_count, 0),
       IFNULL(t2.comment_count, 0)
FROM t_user t
LEFT JOIN (
    SELECT user_id, COUNT(*) AS post_count 
    FROM t_post 
    GROUP BY user_id
) t1 ON t.id = t1.user_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS comment_count 
    FROM t_comment 
    GROUP BY user_id
) t2 ON t.id = t2.user_id

Performance comparison:

• Subquery approach: Scan t_post 1 million times + Scan t_comment 1 million times
• GROUP JOIN approach: Scan t_post once + Scan t_comment once

Performance improvement can be 100x or even 1000x!

2.3 Conditional Aggregation: Auto-merge to CASE WHEN

// Count each user's published and draft posts
List<UserDTO> users = easyEntityQuery.queryable(User.class)
    .select(u -> new UserDTOProxy()
        .id().set(u.id())
        .publishedCount().set(u.posts().where(p -> p.status().eq(1)).count())
        .draftCount().set(u.posts().where(p -> p.status().eq(0)).count())
    )
    .toList();

SQL from typical ORMs (two subqueries):

SELECT t.id,
       (SELECT COUNT(*) FROM t_post WHERE user_id = t.id AND status = 1),
       (SELECT COUNT(*) FROM t_post WHERE user_id = t.id AND status = 0)
FROM t_user t

easy-query optimized SQL (single GROUP JOIN + CASE WHEN):

SELECT t.id,
       SUM(CASE WHEN t1.status = 1 THEN 1 ELSE 0 END),
       SUM(CASE WHEN t1.status = 0 THEN 1 ELSE 0 END)
FROM t_user t
LEFT JOIN t_post t1 ON t.id = t1.user_id
GROUP BY t.id

Multiple conditional aggregations on the same table are merged into one JOIN + multiple CASE WHEN expressions.

---

3. Multi-Level Nested Subqueries

3.1 Nested Navigation Properties

// Query users who have posts with liked comments
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().any(p -> p.comments().any(c -> c.likes().any())))
    .toList();

3.2 Cross-Level Aggregation

// Query users whose posts have over 100 total comments
List<User> users = easyEntityQuery.queryable(User.class)
    .where(u -> u.posts().flatElement().comments().count().gt(100L))
    .toList();

---

4. Combining Subqueries with Explicit Joins

// Query users with orders over 1000 who also have comments
List<User> users = easyEntityQuery.queryable(User.class)
    .innerJoin(Order.class, (u, o) -> u.id().eq(o.userId()))
    .where((u, o) -> {
        o.amount().gt(new BigDecimal("1000"));
        u.comments().any();  // Subquery combined with Join
    })
    .select((u, o) -> u)
    .distinct()
    .toList();

---

5. Comparison with Other ORMs

Feature	easy-query	MyBatis-Plus	JPA/Hibernate	jOOQ
Implicit subquery (any/count)	✅ Lambda syntax	❌ Requires raw SQL	❌ Requires JPQL/Criteria	❌ Manual construction
Subquery in WHERE	✅	❌	Partial support	✅ Manual
Subquery in ORDER BY	✅	❌	❌	✅ Manual
Subquery in SELECT	✅	❌	❌	✅ Manual
Subquery to GROUP JOIN	✅ Auto-optimization	❌	❌	❌
Conditional aggregation merge	✅ Auto-optimization	❌	❌	❌

---

6. Why "Most Powerful"?

1. Simplest syntax: u.posts().count().gt(5L) — as natural as speaking
2. Automatic optimization: Subquery to GROUP JOIN, conditional aggregation to CASE WHEN
3. Full scenario support: Subqueries in WHERE, ORDER BY, and SELECT
4. Strongly-typed: Compile-time type checking, refactor-friendly
5. Best performance: Automatically generates high-performance SQL without manual optimization

---

Summary

easy-query's subquery design philosophy:

1. Make complex simple: Express complex subquery logic with Lambda
2. Make slow fast: Automatically optimize inefficient SQL to efficient SQL
3. Make manual automatic: Developers describe "what they want", the framework handles "how to do it"

If your business involves lots of relational statistics, existence checks, and aggregate queries, easy-query's subquery capabilities are worth trying.

---

Related Links

• GitHub: https://github.com/dromara/easy-query
• Documentation: https://www.easy-query.com/easy-query-doc/en