[Java] Mybatis Column '{컬럼명}' in IN/ALL/ANY subquery is ambiguous 해결

서론

Mybatis 코드 개선 작업에서 발생한 이슈를 정리해본다.

Cause: java.sql.SQLIntegrityConstraintViolationException: Column '{컬럼명}' in IN/ALL/ANY subquery is ambiguous;

---

해당 이슈는 여러 테이블과 서브쿼리, 조인(join)을 사용하는 SQL 문에서
동일한 컬럼 이름이 여러 테이블에 존재할 때 발생할 수 있다.

---

문제 발생 SQL 예제

1. 아래 SQL은 서브쿼리 안에서 id 컬럼이 모호한 문제가 있다.
2. SQL 엔진은 서브쿼리 내에서 어느 테이블의 id 컬럼을 사용해야 하는지 결정할 수 없다.

SELECT 
    a.id, 
    a.name, 
    b.status 
FROM 
    table1 a
LEFT JOIN 
    table2 b ON a.id = b.table1_id
LEFT JOIN 
    table3 c ON a.id = c.table1_id
WHERE 
    a.id IN (
        SELECT 
            id 
        FROM 
            table4 
        WHERE 
            status = 'active'
    )
AND 
    b.id IN (
        SELECT 
            id 
        FROM 
            table5 
        WHERE 
            status = 'approved'
    )
AND 
    c.id IN (
        SELECT 
            id 
        FROM 
            table6 
        WHERE 
            status = 'confirmed'
    );

문제 발생 Java Mybatis 예제

package com.example.mapper;

import com.example.model.Example;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface ExampleMapper {

    @Select(
        "<script> "
        + "SELECT "
        + "    a.id, "
        + "    a.name, "
        + "    b.status "
        + "FROM "
        + "    table1 a "
        + "LEFT JOIN "
        + "    table2 b ON a.id = b.table1_id "
        + "LEFT JOIN "
        + "    table3 c ON a.id = c.table1_id "
        + "WHERE "
        + "    a.id IN ("
        + "        SELECT "
        + "            id "
        + "        FROM "
        + "            table4 "
        + "        WHERE "
        + "            status = 'active' "
        + "    ) "
        + "AND "
        + "    b.id IN ("
        + "        SELECT "
        + "            id "
        + "        FROM "
        + "            table5 "
        + "        WHERE "
        + "            status = 'approved' "
        + "    ) "
        + "AND "
        + "    c.id IN ("
        + "        SELECT "
        + "            id "
        + "        FROM "
        + "            table6 "
        + "        WHERE "
        + "            status = 'confirmed' "
        + "    ); "
        + "</script>"
    )
    @Results({
        @Result(id = true, property = "id", column = "id"),
        @Result(property = "name", column = "name"),
        @Result(property = "status", column = "status")
    })
    List<Example> getExamples();
}

 

---

해결 SQL 예제

1. 해결 방법은 서브쿼리 내에서 모호한 컬럼에 대해 테이블 별칭을 명시적으로 지정하는 것입니다.
2. 서브쿼리에서 별칭 사용
   • 각 서브쿼리는 이제 해당 테이블에 대해 별칭(t4, t5, t6)을 사용한다.
   • 각 서브쿼리 내 id 컬럼은 해당 테이블 별칭으로 명확히 지정된다.
3. 일관된 별칭 사용
   • 메인 쿼리에서도 이미 테이블 별칭(a, b, c)을 사용하고 있어 SQL 문 전반에 걸쳐 일관성을 유지한다.

SELECT 
    a.id, 
    a.name, 
    b.status 
FROM 
    table1 a
LEFT JOIN 
    table2 b ON a.id = b.table1_id
LEFT JOIN 
    table3 c ON a.id = c.table1_id
WHERE 
    a.id IN (
        SELECT 
            t4.id 
        FROM 
            table4 t4
        WHERE 
            t4.status = 'active'
    )
AND 
    b.id IN (
        SELECT 
            t5.id 
        FROM 
            table5 t5
        WHERE 
            t5.status = 'approved'
    )
AND 
    c.id IN (
        SELECT 
            t6.id 
        FROM 
            table6 t6
        WHERE 
            t6.status = 'confirmed'
    );

문제 발생 Java Mybatis 예제

package com.example.mapper;

import com.example.model.Example;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface ExampleMapper {

    @Select(
        "<script>"
        + "SELECT "
        + "    a.id, "
        + "    a.name, "
        + "    b.status "
        + "FROM "
        + "    table1 a "
        + "LEFT JOIN "
        + "    table2 b ON a.id = b.table1_id "
        + "LEFT JOIN "
        + "    table3 c ON a.id = c.table1_id "
        + "WHERE "
        + "    a.id IN ("
        + "        SELECT "
        + "            t4.id "
        + "        FROM "
        + "            table4 t4 "
        + "        WHERE "
        + "            t4.status = 'active' "
        + "    ) "
        + "AND "
        + "    b.id IN ("
        + "        SELECT "
        + "            t5.id "
        + "        FROM "
        + "            table5 t5 "
        + "        WHERE "
        + "            t5.status = 'approved' "
        + "    ) "
        + "AND "
        + "    c.id IN ("
        + "        SELECT "
        + "            t6.id "
        + "        FROM "
        + "            table6 t6 "
        + "        WHERE "
        + "            t6.status = 'confirmed' "
        + "    );"
        + "</script>"
    )
    @Results({
        @Result(id = true, property = "id", column = "id"),
        @Result(property = "name", column = "name"),
        @Result(property = "status", column = "status")
    })
    List<Example> getExamples();
}