Oracle vs PostgreSQL SQL 문법 차이점

기존에 작성되어 있던 쿼리를 개선하면서 Oracle, PostgreSQL 의 문법에 약간씩 차이가 있는걸 정리해보았다.

1️⃣ Left Join

[ Oracle ]

LEFT JOIN TSOL_CO_CPTY_ROL_SFS ROL

• Oracle에서는 LEFT JOIN을 사용하여 테이블을 조인함

• LEFT OUTER JOIN을 명시적으로 사용해도 되지만, LEFT JOIN만 사용해도 동일하게 동작함

[ PostgreSQL ]

LEFT OUTER JOIN TSOL_CO_CPTY_ROL_SFS ROL

• PostgreSQL에서는 LEFT JOIN을 사용할 수 있지만, 명확하게 LEFT OUTER JOIN을 명시하는 경우가 많음

✅ 차이점: PostgreSQL은 OUTER 키워드를 더 선호하지만 필수는 아님.

---

2️⃣ LIKE 연산자 사용 차이 (숫자형과 LIKE)

[ Oracle ]

AND BSC.CPTY_NO LIKE TO_CHAR(#{cptyNo, jdbcType=NUMERIC}) || '%'

• Oracle에서는 LIKE 연산을 사용할 때, 숫자 타입을 TO_CHAR로 변환해야 함

• 숫자 값을 직접 LIKE와 함께 사용하면 "문자가 부적합합니다" 오류가 발생할 수 있음

[ PostgreSQL ]

AND BSC.CPTY_NO LIKE #{cptyNo, jdbcType=NUMERIC} || '%'

• PostgreSQL에서는 숫자 타입을 자동으로 문자열로 변환하는 기능이 있어, TO_CHAR 변환이 필요없음

✅ 차이점:

• Oracle: TO_CHAR(숫자컬럼) LIKE '값%' 필요

• PostgreSQL: 숫자컬럼 LIKE '값%' 가능 (자동 변환)

---

3️⃣ NULL 비교 방식 차이 (COALESCE vs NVL)

[ Oracle ]

NVL(ROL.CPTY_ROL_TC, '10')

• Oracle에서는 NVL을 사용하여 NULL 값을 기본값으로 대체함

[ PostgreSQL ]

COALESCE(ROL.CPTY_ROL_TC, '10')

• PostgreSQL에서는 COALESCE를 사용하여 NULL 값을 기본값으로 대체함

✅ 차이점:

• Oracle → NVL(컬럼, 기본값)

• PostgreSQL → COALESCE(컬럼, 기본값)

---

4️⃣ UPPER vs NLSSORT (대소문자 무시 비교)

[ Oracle ]

AND NLSSORT(BSC.CPTY_NM, 'NLS_SORT = BINARY_CI') 
    LIKE NLSSORT('%' || #{cptyNm, jdbcType=VARCHAR} || '%', 'NLS_SORT = BINARY_CI')

• Oracle에서는 대소문자를 구분하지 않는 비교를 위해 NLSSORT를 사용해야함

• NLS_SORT = BINARY_CI 옵션을 적용해야 LIKE 검색이 대소문자를 무시하고 수행됨

[ PostgreSQL ]

AND UPPER(BSC.CPTY_NM) LIKE '%' || UPPER(#{cptyNm, jdbcType=VARCHAR}) || '%'

• PostgreSQL에서는 LIKE 연산에서 대소문자 비교를 무시하기 위해 UPPER()를 사용함

✅ 차이점:

• Oracle → NLSSORT(컬럼, 'NLS_SORT = BINARY_CI')

• PostgreSQL → UPPER(컬럼)

---

5️⃣ 문자열 연결 연산자 (|| vs CONCAT)

[ Oracle & PostgreSQL ]

BSC.CPTY_NM LIKE '%' || #{cptyNm, jdbcType=VARCHAR} || '%'

• Oracle과 PostgreSQL 모두 || 연산자를 사용하여 문자열을 연결함

• PostgreSQL에서는 CONCAT() 함수를 사용할 수도 있음

✅ 차이점:

• Oracle & PostgreSQL → 컬럼 || '문자열'

• PostgreSQL 추가 기능 → CONCAT(컬럼, '문자열') 가능

---

6️⃣ NUMERIC 데이터 타입 처리

[ Oracle ]

AND BSC.VRS = #{vrs, jdbcType=NUMERIC}

• Oracle에서 NUMERIC 타입을 사용할 때 NUMBER 타입과 자동 변환됨

[ PostgreSQL ]

AND BSC.VRS = #{vrs, jdbcType=NUMERIC}

• PostgreSQL에서는 NUMERIC과 INTEGER 타입을 구분해야함

• ::NUMERIC과 같은 명시적 형변환이 필요할 수도 있음

✅ 차이점:

• Oracle → NUMBER 타입을 자동 처리

• PostgreSQL → NUMERIC과 INTEGER를 구분하여 처리

---

📌 최종 정리 (Oracle vs PostgreSQL 문법 차이점)

차이점	Oracle	PostgreSQL
LEFT JOIN 문법	LEFT JOIN	LEFT OUTER JOIN 가능
LIKE 연산자와 숫자	TO_CHAR(숫자) LIKE '값%'	숫자 LIKE '값%' 가능
NULL 처리 함수	NVL(컬럼, 기본값)	COALESCE(컬럼, 기본값)
대소문자 무시 검색	NLSSORT(컬럼, 'NLS_SORT = BINARY_CI')	UPPER(컬럼) LIKE UPPER(값)
문자열 연결	컬럼 || '문자열'	컬럼 || '문자열' CONCAT(컬럼, '문자열') 가능
숫자 타입 (NUMERIC)	NUMBER 자동 처리	NUMERIC과 INTEGER 구분 필요