회원이 설문조사한 결과를 기반 결과 반환 SQL 쿼리문 분석하기

원하는 기능

회원이 설문조사한 결과를 기반으로 영양제 데이터 베이스의 내장되어있는 기능성 테이블과 비교해서 겹치는 것이 1개라고 있으면 반환하는 것 입니다.

테이블 구조

사용되는 테이블은 총 4개입니다.

회원의 테이블은 vita_member
회원의 기능성 설문조사가 저장된 테이블은 vita_sur_function
영양제 이름과 카테고리등이 답겨져있는 테이블은 vita_vita
영양제의 기능성이 있는 테이블은 vita_fuction입니다.

vita_vita와vita_fuction은 vno로 연결되어 있고

vita_member와 vita_sur_function은 id로 연결되어 있습니다.

SQL 쿼리문

 <select id="getRecommendedVitamin" resultType="com.sejong.vitaweb.vo.Vitamin">
        SELECT v.*
        FROM vita_vita v
                 JOIN Vita_Function vf ON v.vno = vf.vno
        WHERE EXISTS (
                      SELECT 1
                      FROM Vita_Sur_Function vsf
                      WHERE vsf.id = #{id}
                        AND (
                              (vsf.sc = 1 AND vf.sc = 1)
                              OR (vsf.act = 1 AND vf.act = 1)
                              OR (vsf.eye = 1 AND vf.eye = 1)
                              OR (vsf.joint = 1 AND vf.joint = 1)
                              OR (vsf.oxy = 1 AND vf.oxy = 1)
                              OR (vsf.sight = 1 AND vf.sight = 1)
                              OR (vsf.skin = 1 AND vf.skin = 1)
                              OR (vsf.imn = 1 AND vf.imn = 1)
                              OR (vsf.jang = 1 AND vf.jang = 1)
                              OR (vsf.gan = 1 AND vf.gan = 1)
                              OR (vsf.prs = 1 AND vf.prs = 1)
                              OR (vsf.bone = 1 AND vf.bone = 1)
                              OR (vsf.col = 1 AND vf.col = 1)
                              OR (vsf.vmid = 1 AND vf.vmid = 1)
                          )
                  )
    </select>

쿼리문 분석

SELECT v.*

select는 sql에서 데이터베이스에서 필요한 데이터를 선택하기 위해 사용됩니다.
v.*는 v이라는 테이블의 모든 열(column)을 선택한다라는 뜻입니다. 여기서 v는 vita_vita테이블을 가리키는 별칭(alias)입니다.

별칭(alias)?
다른 프로그래밍언어에서 쓰이는 변수같은 개념이라고 생각하시면 됩니다.
마치 for(int i=0; i<n;i++) 에서의 i 같은 존재입니다.

FROM vita_vita v

FROM은 어떤 테이블에서 데이터를 선택할 것인지 명시합니다. vita_vita v에서 v는 vita_vita 테이블의 별칭입니다.
그러니 이 코드는 vita_vita에서 가져온다는 뜻입니다.

JOIN Vita_Function vf ON v.vno = vf.vno

JOIN은 두 개 이상의 테이블에서 데이터를 결합하는 데 사용됩니다. 여기서는 vita_vita 테이블과 Vita_Function 테이블을 vno 컬럼을 기준으로 결합하고 있습니다.

---

JOIN을 왜 하는거지?

여기서 JOIN은 vita_vita 테이블과 Vita_Function 테이블을 연결하는데 사용됩니다. 이 두 테이블을 조인하는 이유는 각 테이블에 있는 데이터를 기반으로 정보를 결합하고, 특정 조건을 만족하는 결과를 반환하기 위함입니다.

vita_vita 테이블은 비타민에 대한 정보를 가지고 있고, Vita_Function 테이블은 각 비타민의 기능에 대한 정보를 가지고 있습니다. 따라서 사용자의 설문조사 결과에 따라 적합한 비타민을 찾기 위해서는 이 두 테이블을 조인해야 합니다.

vita_vita 테이블과 Vita_Function 테이블은 vno라는 공통 필드를 통해 연결됩니다. 이 필드는 두 테이블 사이의 관계를 정의하고, 이를 통해 조인을 수행합니다. 따라서 JOIN 절은 vno 필드를 기준으로 두 테이블을 결합하며, 이로 인해 각 비타민에 대한 정보와 해당 비타민의 기능 정보를 한 데 모을 수 있습니다.

JOIN 안하고도 가능한거아닌가? 왜 하는거지?

JOIN을 사용하는 주요 이유는 효율성과 가독성입니다.

데이터베이스에 저장된 테이블 간에 관계가 있을 경우, 특정 작업을 수행하기 위해서는 여러 테이블에서 데이터를 가져와야 할 수 있습니다. 이 경우, 각 테이블에서 별도로 데이터를 조회하고 이를 가져와서 어플리케이션 레벨(스프링 같은 곳)에서 조합하는 것보다, 데이터베이스 레벨에서 JOIN을 사용하여 한 번의 쿼리로 필요한 데이터를 모두 가져오는 것이 더 효율적입니다.

예를 들어, 여기서는 vita_vita 테이블과 Vita_Function 테이블에서 데이터를 가져와야 합니다. 만약 JOIN을 사용하지 않고 별도로 두 테이블에서 데이터를 조회한다면, 데이터베이스에서 두 번의 쿼리를 수행하고, 그 결과를 어플리케이션 레벨에서 비교하고 조합해야 합니다. 이는 코드가 복잡해지고, 불필요하게 많은 데이터를 메모리에 로드하게 될 수 있습니다.

또한, JOIN을 사용하면 쿼리의 결과가 어떻게 결정되는지 더 명확하게 이해할 수 있습니다. JOIN 조건은 두 테이블이 어떻게 연결되는지를 명시적으로 보여줍니다. 따라서 쿼리를 읽는 사람이 이를 더 쉽게 이해할 수 있습니다.

그러므로, 여러 테이블에서 데이터를 가져와야 하는 경우에는 JOIN을 사용하는 것이 일반적으로 좋습니다.

어플리케이션 레벨?

"어플리케이션 레벨"은 일반적으로 개발 스택의 여러 계층 중 하나를 지칭하는데, 이 경우에는 사용자 인터페이스(UI)와 데이터베이스 사이에 위치한 계층을 가리킵니다.

프로그래밍에서 애플리케이션 레벨은 데이터 처리, 사용자 입력 처리, 비즈니스 로직 구현 등과 같은 작업을 포함하는 코드 레벨을 가리킵니다. 이 계층에서 개발자는 데이터베이스에서 정보를 조회하거나 업데이트하거나, 사용자로부터 입력을 받거나, 이러한 정보를 바탕으로 복잡한 계산이나 결정을 하는 등의 작업을 수행합니다.

이를 통해 사용자 인터페이스 (프론트 엔드)와 데이터베이스 (백엔드) 사이의 '브릿지' 역할을 하며, 이러한 프로세스와 연산들은 보통 서버 사이드에서 이루어집니다.

그래서 앞서 "어플리케이션 레벨에서 조합한다"라는 말은, 서버 사이드 코드에서 데이터를 처리하고 합치는 작업을 수행한다는 것을 의미합니다. 그러나 이런 작업은 보통 데이터베이스 쿼리를 통해 더 효율적으로 수행될 수 있습니다.

---

WHERE EXISTS ( ... )

WHERE는 특정 조건을 만족하는 행(row)만 선택하라는 뜻입니다. EXISTS는 괄호 안에 있는 서브쿼리가 결과를 반환하면 참(true), 그렇지 않으면(false)입니다.

SELECT 1 FROM Vita_Sur_Function vsf WHERE vsf.id = #{id} AND (...)

이 부분은 서브쿼리로, Vita_Sur_Function 테이블에서 특정 조건을 만족하는 행을 찾습니다. vsf.id = #{id}는 사용자의 ID에 해당하는 행만 선택하라는 뜻입니다.

Select 1 ??

SELECT 1은 매우 간단한 SQL 쿼리로, 본질적으로 상수 1을 반환합니다.

이 구문은 EXISTS 절과 함께 사용될 때 자주 볼 수 있습니다. EXISTS 절은 괄호 안의 서브쿼리가 결과를 반환하면 참(true)이고, 아무 것도 반환하지 않으면 거짓(false)입니다.

SELECT 1은 결과를 반환하는지 여부만 중요할 때 사용됩니다. 이 쿼리의 목적은 실제로 '1' 값을 반환하는 것이 아니라, WHERE 절에 있는 조건을 만족하는 데이터가 Vita_Sur_Function 테이블에 존재하는지 확인하는 것입니다.

예를 들어, 아래와 같은 쿼리는 Vita_Sur_Function 테이블에 사용자 아이디가 있는지 확인하는데 사용됩니다:

WHERE EXISTS (SELECT 1 FROM Vita_Sur_Function vsf WHERE vsf.id = #{id} ...)

이 경우, vsf.id = #{id} 조건을 만족하는 행이 Vita_Sur_Function 테이블에 존재하면, EXISTS 절은 참이 되고 그렇지 않으면 거짓이 됩니다. 즉, SELECT 1 자체의 반환값보다는 그 존재 여부가 중요한 것입니다.

(vsf.sc = 1 AND vf.sc = 1) OR (vsf.act = 1 AND vf.act = 1) OR ...

이 부분은 사용자의 설문조사 응답과 영양제의 기능을 비교하여 일치하는 영양제를 찾는 조건입니다. AND는 두 조건이 모두 참일 때 참이 됩니다. OR는 두 조건 중 하나만 참이어도 참이 됩니다.

---

결론 및 요약

이 SQL 쿼리는 vita_vita 테이블(영양제 정보를 담고 있는 테이블)에서 특정 사용자(id로 지정)가 선택한 기능에 맞는 영양제를 찾는 데 사용됩니다.

먼저, SELECT v.* FROM vita_vita v JOIN Vita_Function vf ON v.vno = vf.vno 부분에서 vita_vita와 Vita_Function 테이블을 vno를 통해 조인(join)하고 있습니다. 즉, 각 영양제와 그에 대응하는 기능성 데이터를 연결하고 있습니다.

WHERE EXISTS (SELECT 1 FROM Vita_Sur_Function vsf WHERE vsf.id = #{id} AND (...)) 부분은 사용자(id로 지정)의 설문조사 응답을 참조하여 해당 사용자가 선택한 기능이 있는지 확인합니다.

내부에 있는 많은 OR 조건들은 각각의 기능에 해당하는 설문조사 응답과 영양제의 기능성 데이터를 비교하고 있습니다. 예를 들어 (vsf.sc = 1 AND vf.sc = 1) 부분은 사용자가 스트레스 케어 기능을 선택했고 (vsf.sc = 1), 선택한 영양제가 스트레스 케어 기능이 있는지 (vf.sc = 1) 확인합니다.

즉, 이 쿼리는 사용자가 설문조사를 통해 선택한 각 기능에 대응하는 영양제를 찾아 반환합니다.

이 코드를 사용하면 결국 회원의 설문조사 테이블과 비타민의 기능성 테이블을 비교하여 OR, 즉 회원이 고른 기능성에 대하여 반환하는 결과를 가져옵니다.