JPQL vs SQL in JPA

Direct Answer

JPQL names entities and their mapped attributes; SQL names database tables, columns, and functions. The JPA provider translates JPQL into SQL for the configured dialect. JPQL is more portable for supported standard behavior, but database schemas, functions, null/collation rules, provider extensions, and generated plans still differ, so it is not a guarantee of database-independent behavior.

Code Example

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Join across a relationship
    @Query("SELECT o FROM Order o JOIN o.user u WHERE u.email = :email")
    List<Order> findByUserEmail(@Param("email") String email);

    // Aggregate
    @Query("SELECT COUNT(o) FROM Order o WHERE o.status = :status")
    long countByStatus(@Param("status") OrderStatus status);

    // JOIN FETCH solves N+1 by loading the collection in one query
    @Query("SELECT o FROM Order o JOIN FETCH o.lineItems WHERE o.id = :id")
    Optional<Order> findWithItems(@Param("id") Long id);

    // Constructor expression → DTO projection
    @Query("SELECT new com.example.dto.OrderSummary(o.id, o.total) FROM Order o")
    List<OrderSummary> summaries();
}

Common Mistakes & Pitfalls

• Table names instead of entities — FROM users fails; it must be FROM User.
• Column names instead of fields — use u.email (Java field), not u.email_address (DB column).
• Cross-join SQL style — write JOIN u.orders o, not FROM User u, Order o WHERE ....
• Forgetting JOIN FETCH — a plain JOIN still triggers N+1 when you access the relation later.