Table of Contents

1. A Left Join followed by an Inner Join behaves like a regular Inner Join
2. When pagination is used, avoid using groupBy or associateBy in the app layer – use SQL GROUP BY instead
3. Understand SQL query execution order
4. Adding AND conditions in LEFT JOIN ON does not affect row count
5. For one-to-one relationships, use "@OneToOne" and enforce a UNIQUE KEY in the database
6. "@Transactional" only guarantees atomicity in specific situations
7. WHERE vs HAVING

1. A Left Join followed by an Inner Join behaves like a regular Inner Join

If a regular JOIN is mixed after a LEFT JOIN chain, the following JOIN behaves as an INNER JOIN.

Example

SELECT *
FROM user u
LEFT JOIN order o ON u.id = o.user_id
JOIN product p ON o.product_id = p.id

• Since product is joined with a regular JOIN, it acts as an INNER JOIN, so the entire query behaves like an INNER JOIN.
• Reference: Caution when using LEFT JOIN (Korean)

2. Avoid using groupBy/associateBy in the app layer when pagination is applied

If you paginate using SQL (LIMIT, OFFSET) and then group data in the app layer using groupBy or associateBy, the number of items per page becomes inconsistent, making pagination meaningless.

Example (Kotlin)

val orders = fetchPageFromDB(limit = 10, offset = 0)
val grouped = orders.groupBy { it.userId }
println(grouped.size) // Result: 6 groups

→ Next page:

val orders = fetchPageFromDB(limit = 10, offset = 10)
val grouped = orders.groupBy { it.userId }
println(grouped.size) // Result: 3 groups

• Inconsistent item count per page → user confusion → Pagination should be based on SQL results to guarantee consistency

3. Understand SQL Query Execution Order

SQL runs in the following order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Understanding this order helps in predicting query results and writing more effective queries.

It’s recommended to practice by creating small test tables (e.g., 5 rows) with various relationships (1:1, 1:N, N:M) and experimenting with different query combinations to predict results instead of just executing and checking.

4. Adding AND conditions in LEFT JOIN ON does not affect row count

If you put conditions in the ON clause with AND in a LEFT JOIN, it doesn't filter rows – it just returns NULL in the joined table if the condition doesn’t match.

To filter rows, you must use the WHERE clause.

Some developers overuse AND thinking it’s more performant than WHERE,

but in LEFT JOIN, AND does not filter base rows.

Example 1: LEFT JOIN with ON ... AND

SELECT u.id AS user_id, u.name AS user_name, o.id AS order_id, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'DELIVERED';

Results:

• Alice has orders with statuses 'DELIVERED' and 'PENDING'.
  • The 'DELIVERED' one is joined.
  • The 'PENDING' one shows up as NULL.
• Bob and Carol have no matching 'DELIVERED' orders → NULL.

Example 2: INNER JOIN with ON ... AND

SELECT u.id AS user_id, u.name AS user_name, o.id AS order_id, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id AND o.status = 'DELIVERED';

Only Alice’s 'DELIVERED' order is shown.

Example 3: LEFT JOIN with WHERE

SELECT u.id AS user_id, u.name AS user_name, o.id AS order_id, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'DELIVERED';

Even though it's a LEFT JOIN, the WHERE condition filters rows like an INNER JOIN.

Summary Table

5. For one-to-one relationships, use OneToOne and enforce UNIQUE KEY in DB

Don’t use ManyToOne for one-to-one mappings.

Kotlin Example

@OneToOne
(name = "profile_id", unique = true)
val profile: UserProfile

6. "@Transactional" only guarantees atomicity in specific situations

(1) Rollback only occurs for RuntimeException by default

To rollback on all exceptions, specify explicitly:

kotlin@Transactional(rollbackFor = [Exception::class])

(2) While "@Transactional" is running, other requests are not blocked

• Even inside the same application, transactional behavior depends on the isolation level set in application.properties and the DB.
• In a load-balanced environment, requests may go to multiple instances and concurrently hit the DB.

Example:

1. Transaction A starts

BEGIN;
SELECT MAX(id) FROM user; -- Result: 100
-- Prepares to insert id = 101

2. Transaction B interjects before A commits

BEGIN;
SELECT MAX(id) FROM user; -- Result: 100
INSERT INTO user(id, name) VALUES (101, 'UserB');
COMMIT;

3. Transaction A tries to insert

INSERT INTO user(id, name) VALUES (101, 'UserA');
--  Primary Key conflict!

Table locks may seem like a solution, but they can lead to deadlocks.

That’s why AUTO_INCREMENT or SEQUENCE exists.

The important takeaway is that when multiple users are reading/writing concurrently,

you must understand "transaction isolation level" behavior.

• Usually, REPEATABLE READ is used (must align application.properties with DB setting).
• Personally prefer READ COMMITTED.
• Great reference article (in Korean): https://mangkyu.tistory.com/299

(3) Thus, ensure data integrity with DB constraints + exception handling

try {

    userRepository.save(User(email = "[email protected]"))

} catch (e: DataIntegrityViolationException) {

    // Handle duplicate email

}

(4) Why use "@Transactional"(readOnly = true) for SELECT?

• It improves JPA performance, not DB-level performance.
• See: https://hungseong.tistory.com/74

7. WHERE vs HAVING

Based on SQL execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

1. When using GROUP BY, you must include columns in SELECT or wrap them in aggregation functions.
2. WHERE filters rows before grouping, while HAVING filters after grouping.

Example 1: Using WHERE

SELECT *

FROM order

WHERE status = 'PAID'

Example 2: Using HAVING

SELECT user_id, COUNT() as order_count

FROM order

GROUP BY user_id

HAVING COUNT() > 3

Final Tip

Even when using JPA, copy the generated SQL with ? values from the console, paste them into ChatGPT to fill the parameters,

run the SQL manually, and observe the actual result.

If possible, try checking execution plans later to assess performance.