JPA Left Join IS NULL condition not working

Question

I have User and Employee tables on MySQL, and there is employeeId as foreign key in User table.

Now I need to get Employees who do not have User.

I write this SQL in MySQL Workbench, this works exactly how I want:

SELECT * FROM HUMANRESOURCE.EMPLOYEE E LEFT JOIN AUTHORIZE.USER U 
                                       ON U.EMPLOYEEOBJID = E.OBJID  
                                       WHERE U.EMPLOYEEOBJID is NULL;

But when I try to implement this SQL as JPA query, it returns nothing. Here is JPA Query:

Query query = em.createQuery("SELECT e FROM Employee e LEFT JOIN User u 
                                        WHERE u.employee.objid = e.objid 
                                        AND u.employee IS NULL");

And here is truely working JPA Query that I use for getting Employees who have user:

Query query = em.createQuery("SELECT e FROM Employee e INNER JOIN User u 
                                       WHERE u.employee.objid = e.objid");

What am I doing wrong here?

Update for entity classes:

Base.java

package com.kadir.entity;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.Date;

import javax.persistence.Cacheable;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.persistence.Version;


@Cacheable
@MappedSuperclass
public abstract class Base {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "OBJID")
    private BigInteger objid;

    @Column(name = "CREATEDBY")
    private String createdby;

    @Column(name = "CREATEDDATE")
    private Timestamp createddate;

    @Version
    @Column(name = "ROWVERSION")
    private Integer rowversion;

    @Column(name = "UPDATEDBY")
    private String updatedby;

    @Column(name = "UPDATEDDATE")
    private Timestamp updateddate;

    @Column(name = "ARCHIVED", columnDefinition = "int default 0")
    private int archived;

    public BigInteger getObjid() {
        return this.objid;
    }

    public void setObjid(BigInteger objid) {
        this.objid = objid;
    }

    public String getCreatedby() {
        return this.createdby;
    }

    public void setCreatedby(String createdby) {
        this.createdby = createdby;
    }

    public Date getCreateddate() {
        return this.createddate;
    }

    public void setCreateddate(Timestamp createddate) {
        this.createddate = createddate;
    }

    public Integer getRowversion() {
        return this.rowversion;
    }

    public void setRowversion(Integer rowversion) {
        this.rowversion = rowversion;
    }

    public String getUpdatedby() {
        return this.updatedby;
    }

    public void setUpdatedby(String updatedby) {
        this.updatedby = updatedby;
    }

    public Timestamp getUpdateddate() {
        return this.updateddate;
    }

    public void setUpdateddate(Timestamp updateddate) {
        this.updateddate = updateddate;
    }

    public int getArchived() {
        return archived;
    }

    public void setArchived(int archived) {
        this.archived = archived;
    }
}

Employee.java

package com.kadir.entity.humanresource;

import com.kadir.entity.corporation.Company;
import com.kadir.entity.Base;

import java.io.Serializable;

import javax.persistence.*;


/**
 * The persistent class for the EMPLOYEE database table.
 * 
 */
@Cacheable
@Entity
@Table(name = "EMPLOYEE", schema = "HUMANRESOURCE")
@NamedQuery(name = "Employee.findAll", query = "SELECT e FROM Employee e")
public class Employee extends Base implements Serializable {
    private static final long serialVersionUID = 1L;

    @ManyToOne
    @JoinColumn(name = "COMPANYOBJID")
    private Company company;

    @Column(name = "FIRSTNAME")
    private String firstname;

    @Column(name = "GENDER")
    private int gender;

    @Column(name = "EMAIL")
    private String email;

    @Column(name = "PHONE")
    private String phone;

    @Column(name = "LASTNAME")
    private String lastname;

    public Employee() {
    }

    public Company getCompany() {
        return this.company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public String getFirstname() {
        return this.firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public int getGender() {
        return this.gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return this.email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhone() {
        return this.phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getLastname() {
        return this.lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

}

User.java

package com.kadir.entity.authorize;

import com.kadir.entity.Employee;
import com.kadir.entity.Base;
import java.io.Serializable;
import javax.persistence.*;


import java.util.List;


/**
 * The persistent class for the USER database table.
 * 
 */
@Cacheable
@Entity
@Table(name="USER", schema="AUTHORIZE")
@NamedQuery(name="User.findAll", query="SELECT u FROM User u")
public class User extends Base implements Serializable {
    private static final long serialVersionUID = 1L;

    @OneToOne
    @JoinColumn(name="EMPLOYEEOBJID")
    private Employee employee;

    @Column(name="NAME")
    private String name;

    @Column(name="PASSWORD")
    private String password;

    public User() {
    }

    public Employee getEmployee() {
        return this.employee;
    }

    public void setEmployee(Employee employee) {
        this.employee = employee;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return this.password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

Answer 1

I had similar error. I had conditional OneToOne relation. I fixed problem like that.

    @Query("SELECT e FROM Employee e LEFT OUTER JOIN User u ON  u = e.user " +
        "WHERE (u IS NULL OR e.condition = :conditionValue) ")

Answer 2

In my case, I'm using Apache OpenJPA.

Query should be something like this:

SELECT e FROM Employee e LEFT JOIN e.user u WHERE u.employeeId IS NULL

Answer 3

EclipseLink has support for the ON clause, so try using

"SELECT e FROM Employee e LEFT JOIN User u on u.employee = e WHERE u.employee IS NULL"

You can also use exist and a subquery:

"select e from Employee e where not exists (select 1 from User u where u.employee = e)"