SQLP를 준비하는 mySQL 개발자를 위한 오라클 SQL와 mySQL의 차이

1. NULL 처리

Oracle	MySQL / MariaDB	설명
NVL(expr1, expr2)	IFNULL(expr1, expr2)	NULL이면 다른 값 반환
NVL2(expr, value1, value2)	IF(expr IS NULL, value2, value1)	NULL 여부에 따라 반환
COALESCE()	COALESCE()	동일 (표준 SQL)

Oracle

SELECT NVL(name, 'Unknown')
FROM member;

MySQL

SELECT IFNULL(name, 'Unknown')
FROM member;

표준 SQL

SELECT COALESCE(name, 'Unknown')
FROM member;

---

2. 조건문

Oracle

DECODE(job,
       'MANAGER', '관리자',
       'SALESMAN', '영업',
       '기타')

MySQL

CASE job
    WHEN 'MANAGER' THEN '관리자'
    WHEN 'SALESMAN' THEN '영업'
    ELSE '기타'
END

SQLP에서는 CASE를 더 많이 사용한다.

---

3. 현재 날짜

Oracle	MySQL
SYSDATE	NOW()
CURRENT_DATE	CURRENT_DATE

Oracle

SELECT SYSDATE
FROM dual;

MySQL

SELECT NOW();

---

4. 문자열 연결

Oracle

SELECT first_name || last_name
FROM member;

MySQL

SELECT CONCAT(first_name, last_name)
FROM member;

---

5. LIMIT

Oracle (12c 이상)

SELECT *
FROM member
FETCH FIRST 10 ROWS ONLY;

MySQL

SELECT *
FROM member
LIMIT 10;

---

6. OFFSET

Oracle

SELECT *
FROM member
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

MySQL

SELECT *
FROM member
LIMIT 20,10;

-- 또는
LIMIT 10 OFFSET 20;

---

7. ROWNUM

Oracle

SELECT *
FROM member
WHERE ROWNUM <= 10;

MySQL

LIMIT 10;

---

8. AUTO_INCREMENT

Oracle

CREATE SEQUENCE member_seq;

INSERT INTO member
VALUES(member_seq.NEXTVAL,'Kim');

MySQL

id INT AUTO_INCREMENT PRIMARY KEY

---

9. DUAL 테이블

Oracle

SELECT SYSDATE
FROM dual;

MySQL

-- MySQL에서는 DUAL이 없어도 된다.
SELECT NOW();

---

10. 날짜 더하기

Oracle

SYSDATE + 1

MySQL

DATE_ADD(NOW(), INTERVAL 1 DAY)

---

11. 날짜 차이

Oracle

SYSDATE - hire_date

MySQL

DATEDIFF(NOW(), hire_date)

---

12. 문자열 길이 (동일)

LENGTH(name)

---

13. 대문자/소문자 (동일)

UPPER(name)

LOWER(name)

---

14. 문자열 자르기 (동일)

SUBSTR(name,1,3)

-- MySQL에서는 아래 쿼리도 가능
SUBSTRING(name,1,3)

---

15. 문자열 위치

Oracle

INSTR(name,'A')

MySQL

LOCATE('A',name)

-- 또는
INSTR(name,'A')

---

16. REPLACE (동일)

REPLACE(name,'A','B')

---

17. TRIM (동일)

TRIM(name)

---

18. ROUND (동일)

ROUND(salary)

---

19. CEIL / FLOOR (동일)

CEIL()

FLOOR()

---

20. MOD (동일)

MOD(10,3)

---

21. CAST (동일)

CAST(age AS CHAR)

---

22. TO_CHAR

Oracle

TO_CHAR(salary)

MySQL

CAST(salary AS CHAR)

---

23. TO_DATE

Oracle

TO_DATE('2025-01-01','YYYY-MM-DD')

MySQL

STR_TO_DATE('2025-01-01','%Y-%m-%d')

---

24. TO_NUMBER

Oracle

TO_NUMBER('100')

MySQL

CAST('100' AS SIGNED)

---

25. 실행계획

Oracle

EXPLAIN PLAN FOR
SELECT ...

MySQL

EXPLAIN
SELECT ...

---

26. MERGE

Oracle

MERGE INTO ...

MySQL

INSERT ...
ON DUPLICATE KEY UPDATE

---

27. UPDATE JOIN

Oracle

MERGE

-- 또는
UPDATE
(
    SELECT ...
)

MySQL

UPDATE A
JOIN B
ON ...
SET ...

---

28. DELETE JOIN

Oracle

DELETE
WHERE EXISTS(...)

MySQL

DELETE A
FROM A
JOIN B

---

29. Sequence

Oracle

member_seq.NEXTVAL

member_seq.CURRVAL

MySQL

AUTO_INCREMENT

---

30. Window Function

Oracle

ROW_NUMBER()

RANK()

DENSE_RANK()

LAG()

LEAD()

MySQL

• 8 이상 부터 동일하게 지원

---

31. Common Table Expression

Oracle

WITH temp AS (...)

MySQL

• 8 이상 부터 동일하게 지원

---

32. EXISTS (동일)

WHERE EXISTS (...)

---

33. IN (동일)

WHERE id IN (...)

---

34. ANY / ALL (동일)

> ANY()

> ALL()

---

35. 집계 함수 (동일)

SUM()

AVG()

COUNT()

MAX()

MIN()

---

36. GROUP BY (동일)

GROUP BY

---

37. HAVING (동일)

HAVING COUNT(*) > 10

---

38. JOIN (동일)

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

-- FULL OUTER JOIN은 Oracle만 지원 (mySQL 미지원)

---

39. 계층형 조회

Oracle

CONNECT BY

START WITH

MySQL

WITH RECURSIVE

---

40. Pivot

Oracle

PIVOT

MySQL

CASE

---

SQLP에서 반드시 익혀야 하는 Oracle 문법

• NVL
• DECODE
• CASE
• ROWNUM
• FETCH FIRST
• DUAL
• TO_CHAR
• TO_DATE
• TO_NUMBER
• SEQUENCE
• CONNECT BY
• MERGE