SQL 조회 문법 총정리 2편 (MySQL/Oracle)

🚀

들어가기 전에 🔗

지난

SQL 조회 문법 총정리 1편

에서는 기본적인 SELECT, WHERE, GROUP BY, JOIN 등의 핵심 명령어를 알아보았습니다.

오늘은 여기서 한 단계 더 나아가, 코딩 테스트에서 더욱 복잡하고 까다로운 문제를 해결하는 데 도움이 될

SQL 조회 심화 문법

들을, 특히

MySQL과 Oracle 사용자분들

에게 맞춰 살펴보려고 합니다.

✅

지난 시간 복습 및 예제 테이블 🔗

1편에서 사용했던 Users 테이블과 Orders 테이블을 다시 한번 떠올려보겠습니다.

이 테이블들을 기반으로 심화된 예제들을 살펴볼 예정입니다.

Users 테이블

id(pk) name age email
1 홍길동 25 hong@example.com↗
2 이동훈 27 donghoon099@naver.com↗
3 햄랑이 14 hamlang@ham.ham↗
4 홍길은 25
5 제임스 30 james@example.com↗
6 이몽룡 27 mong@another.com↗

id(pk)	name	age	email
1	홍길동	25	hong@example.com↗
2	이동훈	27	donghoon099@naver.com↗
3	햄랑이	14	hamlang@ham.ham↗
4	홍길은	25
5	제임스	30	james@example.com↗
6	이몽룡	27	mong@another.com↗

Orders 테이블

id(pk)	user_id(fk)	product	order_date	amount
1	1	Apple	2023-01-15	3000
2	2	Banana	2023-01-16	1500
3	1	Orange	2023-01-17	2000
4	3	Pineapple	2023-02-01	4500
5	4	Watermelon	2023-02-05	7000
6	1	Apple	2023-02-10	3000
7	2	Grape	2023-02-15	5000
8	3	Melon	2023-03-01	6000
9	5	Banana	2023-03-05	1500
10	1	Strawberry	2023-03-10	8000
11	NULL	Kiwi	2023-03-12	2500

(Orders 테이블에 order_date와 amount 컬럼을 추가하고, Users 테이블에도 데이터를 추가했습니다.)

🚀

문자열 다루기: CONCAT, SUBSTRING, LENGTH 등 🔗

데이터베이스에는 텍스트 형태의 데이터가 많이 저장됩니다.

이러한 문자열 데이터를 효과적으로 조작하고 분석하기 위한 함수들을 알아봅시다.

MySQL과 Oracle은 유사한 기능을 제공하지만 함수명이나 사용법에 약간의 차이가 있을 수 있습니다.

✅

문자열 합치기: CONCAT, || 🔗

여러 문자열을 하나로 합칠 때 사용합니다.

MySQL
CONCAT() 함수를 주로 사용합니다. || 연산자는 sql_mode 설정에 따라 동작이 다를 수 있어 CONCAT()이 안전합니다.
Oracle
CONCAT() 함수 또는 || 연산자를 모두 사용할 수 있습니다. ||가 더 일반적입니다.

CONCAT(문자열1, 문자열2, ...)는 NULL을 무시하고 합칩니다. ||는 NULL이 포함되면 결과도 NULL이 됩니다.

예시

이름과 이메일을 합쳐서 '이름 (이메일)' 형태로 출력*

MySQL

SELECT CONCAT(name, ' (', COALESCE(email, '이메일 없음'), ')') AS user_profile FROM Users;

Oracle

SELECT name || ' (' || COALESCE(email, '이메일 없음') || ')' AS user_profile FROM Users;
-- 또는 SELECT CONCAT(CONCAT(name, ' ('), COALESCE(email, '이메일 없음') || ')') AS user_profile FROM Users;
-- Oracle의 CONCAT은 두 개의 인자만 받을 수 있어 중첩 사용하거나 ||와 혼용합니다.

user_profile
홍길동 (hong@example.com↗)
이동훈 (donghoon099@naver.com↗)
햄랑이 (hamlang@ham.ham↗)
홍길은 (이메일 없음)
제임스 (james@example.com↗)
이몽룡 (mong@another.com↗)

✅

문자열 자르기 🔗

문자열의 특정 부분만 잘라낼 때 사용합니다.

MySQL
SUBSTRING(문자열, 시작위치, 길이) 또는 SUBSTR(문자열, 시작위치, 길이)
INSTR(문자열, 찾는문자열)로 위치 찾기.
Oracle
SUBSTR(문자열, 시작위치, 길이).
INSTR(문자열, 찾는문자열)로 위치 찾기.

예시

이메일 주소에서 아이디 부분만 추출하기 (@ 앞부분)

MySQL

SELECT email, SUBSTRING(email, 1, INSTR(email, '@') - 1) AS email_id
FROM Users
WHERE email IS NOT NULL AND INSTR(email, '@') > 0;

Oracle

SELECT email, SUBSTR(email, 1, INSTR(email, '@') - 1) AS email_id
FROM Users
WHERE email IS NOT NULL AND INSTR(email, '@') > 0;

email	email_id
hong@example.com↗	hong
donghoon099@naver.com↗	donghoon099
hamlang@ham.ham↗	hamlang
james@example.com↗	james
mong@another.com↗	mong

✅

문자열 길이 🔗

문자열의 길이를 반환합니다.

MySQL
LENGTH() (바이트 단위), CHAR_LENGTH() (문자 단위). 보통 CHAR_LENGTH()를 권장.
Oracle
LENGTH() (문자 단위).

예시

이름이 3글자인 사용자 찾기 (문자 기준)

MySQL

SELECT name, CHAR_LENGTH(name) AS name_length FROM Users WHERE CHAR_LENGTH(name) = 3;

Oracle

SELECT name, LENGTH(name) AS name_length FROM Users WHERE LENGTH(name) = 3;

name	name_length
홍길동	3
이동훈	3
햄랑이	3
제임스	3
이몽룡	3

✅

문자열 치환 🔗

문자열 내의 특정 부분을 다른 문자열로 바꿀 때 사용합니다.

MySQL & Oracle
: REPLACE(대상문자열, 찾는문자열, 바꿀문자열)

예시

이메일 도메인 '.com'을 '.net'으로 변경하기

-- MySQL & Oracle 동일
SELECT email, REPLACE(email, '.com', '.net') AS new_email FROM Users WHERE email LIKE '%.com';

email	new_email
hong@example.com↗	hong@example.net↗
james@example.com↗	james@example.net↗

✅

대소문자 변경 🔗

문자열을 모두 대문자 또는 소문자로 변경합니다.

MySQL & Oracle
: UPPER(문자열), LOWER(문자열)

예시

이름을 모두 대문자로 출력

-- MySQL & Oracle 동일
SELECT name, UPPER(name) AS upper_name FROM Users;

name	upper_name
james	JAMES
... (한글은 변화 없음)

🚀

더 복잡한 JOIN 활용 🔗

JOIN은 대부분의 경우 SQL 표준을 따르므로 MySQL과 Oracle에서 사용법이 거의 동일합니다.

✅

RIGHT JOIN 및 FULL OUTER JOIN 🔗

예시

Orders 테이블 기준으로 모든 주문과 해당 주문자 이름 표시 (주문자가 없는 주문도 포함)

SELECT U.name, O.product, O.order_date
FROM Users U
RIGHT JOIN Orders O ON U.id = O.user_id;

name	product	order_date
홍길동	Apple	2023-01-15
...	...	...
	Kiwi	2023-03-12

✅

SELF JOIN - 자체 조인 🔗

예시를 위해 Employees 테이블을 다음과 같이 가정합니다.
Employees 테이블 (id, name, manager_id)
id name manager_id
1 Alice NULL
2 Bob 1
3 Charlie 1
4 David 2

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2

예시

각 직원의 이름과 그 직원의 매니저 이름 출력하기

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM Employees e1
LEFT JOIN Employees e2 ON e1.manager_id = e2.id;

employee_name	manager_name
Alice
Bob	Alice
Charlie	Alice
David	Bob

✅

여러 조건을 사용하는 JOIN 🔗

MySQL과 Oracle 모두 ON 절에 여러 조건을 사용할 수 있습니다.

예시

Users와 Orders를 조인하되, 특정 사용자(예: 홍길동)의 특정 상품(예: Apple) 주문만 가져오기

SELECT U.name, O.product
FROM Users U
INNER JOIN Orders O ON U.id = O.user_id
WHERE U.name = '홍길동' AND O.product = 'Apple';

name	product
홍길동	Apple
홍길동	Apple

🚀

정렬 및 다른 구조의 SELECT 결과 합치기 🔗

UNION과 UNION ALL도 SQL 표준으로, MySQL과 Oracle에서 동일하게 동작합니다.

✅

UNION 결과 정렬하기 🔗

예시

(SELECT name FROM Users)
UNION
(SELECT name FROM Employees) -- 1편의 Employees 테이블 예시
ORDER BY name ASC;

name
김철수
... (정렬된 결과)

✅

컬럼 개수 및 타입 일치시키기 🔗

타입 변환 시 함수명이 다를 수 있습니다.

MySQL
CAST(값 AS 타입), CONVERT(값, 타입)
Oracle
CAST(값 AS 타입), TO_CHAR(), TO_NUMBER(), TO_DATE() 등

예시

Users 테이블의 이름(문자열)과 Orders 테이블의 주문 수량(숫자)을 합쳐서 보기

MySQL

SELECT name AS item, 'User' AS type FROM Users
UNION ALL
SELECT CAST(amount AS CHAR) AS item, 'Order Amount' AS type FROM Orders;

Oracle

SELECT name AS item, 'User' AS type FROM Users
UNION ALL
SELECT TO_CHAR(amount) AS item, 'Order Amount' AS type FROM Orders;

item	type
홍길동	User
...	...
3000	Order Amount
...	...

🚀

윈도우 함수 (Window Functions) 🔗

윈도우 함수는 SQL에서 특정 행을 기준으로 계산을 수행할 수 있습니다.

사용법은 함수 명과 괄호로 시작하며, OVER() 절을 사용하여 윈도우를 정의합니다.

Window

✅

기본 구조 및 주요 순위 함수 🔗

ROW_NUMBER(), RANK(), DENSE_RANK()는 MySQL과 Oracle 모두 동일하게 사용합니다.

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

RANK() OVER (PARTITION BY ... ORDER BY ...)

DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)

PARTITION BY는 그룹화 기준을 설정하고, ORDER BY는 정렬 기준을 설정합니다.
ROW_NUMBER()는 각 행에 고유한 번호를 부여합니다.
RANK()는 동일한 값에 대해 동일한 순위를 부여하지만, 다음 순위는 건너뜁니다.
DENSE_RANK()는 동일한 값에 대해 동일한 순위를 부여하지만, 다음 순위는 건너뛰지 않습니다.

예시

사용자(Users)를 나이(age)가 많은 순으로 순위 매기기

SELECT
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num,
    RANK() OVER (ORDER BY age DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rnk
FROM Users;

name	age	row_num	rnk	dense_rnk
제임스	30	1	1	1
이동훈	27	2	2	2
...

✅

집계 함수와 함께 사용 🔗

SUM() OVER (...), AVG() OVER (...) 등도 MySQL과 Oracle에서 동일하게 사용합니다.

이 함수들은 특정 그룹 내에서 집계된 값을 계산할 수 있습니다.

SUM(값) OVER (PARTITION BY ... ORDER BY ...)
PARTITION BY는 그룹화 기준을 설정하고, ORDER BY는 정렬 기준을 설정합니다.
ROWS BETWEEN 절을 사용하여 윈도우의 범위를 지정할 수 있습니다.
UNBOUNDED PRECEDING는 시작점, CURRENT ROW는 현재 행, UNBOUNDED FOLLOWING은 끝점을 의미합니다.
RANGE BETWEEN은 값의 범위를 지정합니다.
ROWS는 행의 개수를 기준으로 범위를 지정합니다.

예시

각 사용자의 주문(Orders)에 대해, 해당 사용자의 총 주문 금액과 함께 각 주문 정보 표시

SELECT
    O.id AS order_id,
    U.name AS user_name,
    O.product,
    O.amount,
    SUM(O.amount) OVER (PARTITION BY U.id) AS user_total_amount
FROM Orders O
JOIN Users U ON O.user_id = U.id
ORDER BY U.name, O.id;

order_id	user_name	product	amount	user_total_amount
1	홍길동	Apple	3000	13000
3	홍길동	Orange	2000	13000
6	홍길동	Apple	3000	13000
10	홍길동	Strawberry	8000	13000
2	이동훈	Banana	1500	6500
7	이동훈	Grape	5000	6500
5	홍길은	Watermelon	7000	7000
...

✅

LAG() / LEAD() 🔗

LAG()와 LEAD() 함수도 MySQL과 Oracle에서 동일하게 사용합니다.

이 함수들은 현재 행을 기준으로 이전 또는 이후의 행 값을 가져오는 데 사용됩니다.

LAG(값, 오프셋, 기본값) OVER (PARTITION BY ... ORDER BY ...)
LEAD(값, 오프셋, 기본값) OVER (PARTITION BY ... ORDER BY ...)
PARTITION BY는 그룹화 기준을 설정하고, ORDER BY는 정렬 기준을 설정합니다.
오프셋은 몇 행 전/후의 값을 가져올지를 설정합니다. 기본값은 1입니다.
기본값은 해당 행이 없을 경우 반환할 값을 설정합니다. 기본값을 설정하지 않으면 NULL이 반환됩니다.

예시

각 주문에 대해, 해당 사용자의 바로 이전 주문일 가져오기

SELECT
    O.id AS order_id,
    U.name AS user_name,
    O.order_date,
    LAG(O.order_date, 1, NULL) OVER (PARTITION BY U.id ORDER BY O.order_date) AS previous_order_date
FROM Orders O
JOIN Users U ON O.user_id = U.id
ORDER BY U.name, O.order_date;

order_id	user_name	order_date	previous_order_date
1	홍길동	2023-01-15	NULL
3	홍길동	2023-01-17	2023-01-15
6	홍길동	2023-02-10	2023-01-17
...

🚀

날짜 및 시간 함수 다루기 🔗

날짜/시간 함수는 DBMS별 차이가 큰 부분 중 하나입니다.

✅

현재 날짜/시간 얻기 🔗

MySQL
```
NOW()
CURDATE()
CURTIME()
```
Oracle
```
SYSDATE
CURRENT_DATE
CURRENT_TIMESTAMP
```

✅

날짜/시간 형식 변환 🔗

MySQL
DATE_FORMAT(날짜, 형식)으로 날짜를 원하는 형식으로 변환합니다.
- 예시: '%Y-%m-%d %H:%i:%s' (2023-01-15 12:30:45)
Oracle
TO_CHAR(날짜, 형식)으로 날짜를 원하는 형식으로 변환합니다.
- 예시: 'YYYY-MM-DD HH24:MI:SS' (2023-01-15 12:30:45)

✅

날짜/시간 부분 추출 🔗

MySQL
EXTRACT(부분 FROM 날짜), DATE_FORMAT(날짜, 형식)으로 날짜의 특정 부분을 추출합니다.
```
YEAR(날짜)
MONTH(날짜)
DAY(날짜)
HOUR(시간)
MINUTE(시간)
SECOND(시간)
DAYNAME(날짜)
```

Oracle

EXTRACT(부분 FROM 날짜), TO_CHAR(날짜, 형식)으로 날짜의 특정 부분을 추출합니다.

EXTRACT(부분 FROM 날짜)
TO_CHAR(날짜, 'YYYY')
TO_CHAR(날짜, 'MM')
TO_CHAR(날짜, 'DD')
TO_CHAR(날짜, 'HH24')
TO_CHAR(날짜, 'MI')
TO_CHAR(날짜, 'SS')
TO_CHAR(날짜, 'DAY') -- (요일 이름)

예시

각 주문이 일어난 연도, 월, 요일 이름 추출하기

MySQL

SELECT
    order_date,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAYNAME(order_date) AS order_dayname
FROM Orders;

Oracle

SELECT
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    TO_CHAR(order_date, 'FMDay') AS order_dayname -- 'FM'은 앞뒤 공백 제거
FROM Orders;

order_date	order_year	order_month	order_dayname
2023-01-15	2023	1	Sunday
...	...	...	...

✅

날짜/시간 연산 🔗

MySQL

DATE_ADD(날짜, INTERVAL 값 단위)
DATE_SUB(날짜, INTERVAL 값 단위)
DATEDIFF(날짜1, 날짜2) -- (일수 차이)

Oracle

날짜 + 숫자 -- (일 단위 덧셈)
날짜 - 숫자 -- (일 단위 뺄셈)
ADD_MONTHS(날짜, 개월수)
날짜1 - 날짜2 -- (일수 차이)

예시

주문일로부터 7일 후 날짜 계산하기

MySQL

SELECT order_date, DATE_ADD(order_date, INTERVAL 7 DAY) AS after_7_days FROM Orders;

Oracle

SELECT order_date, order_date + 7 AS after_7_days FROM Orders;

order_date	after_7_days
2023-01-15	2023-01-22
...	...

✅

참고 🔗

W3Schools SQL Tutorial↗
MySQL 공식 문서: https://dev.mysql.com/doc/↗
Oracle 공식 문서: https://docs.oracle.com/en/database/oracle/oracle-database/↗
Mode Analytics SQL Tutorial: https://mode.com/sql-tutorial/↗ (영문이지만 좋은 예제가 많습니다.)

추천 포스트

SQL 조회 문법 총정리 2편 (MySQL/Oracle)Advanced SQL Query Syntax (MySQL/Oracle)

들어가기 전에 🔗

지난 시간 복습 및 예제 테이블 🔗

문자열 다루기: CONCAT, SUBSTRING, LENGTH 등 🔗

문자열 합치기: CONCAT, || 🔗

문자열 자르기 🔗

문자열 길이 🔗

문자열 치환 🔗

대소문자 변경 🔗

더 복잡한 JOIN 활용 🔗

RIGHT JOIN 및 FULL OUTER JOIN 🔗

SELF JOIN - 자체 조인 🔗

여러 조건을 사용하는 JOIN 🔗

정렬 및 다른 구조의 SELECT 결과 합치기 🔗

UNION 결과 정렬하기 🔗

컬럼 개수 및 타입 일치시키기 🔗

윈도우 함수 (Window Functions) 🔗

기본 구조 및 주요 순위 함수 🔗

집계 함수와 함께 사용 🔗

LAG() / LEAD() 🔗

날짜 및 시간 함수 다루기 🔗

현재 날짜/시간 얻기 🔗

날짜/시간 형식 변환 🔗

날짜/시간 부분 추출 🔗

날짜/시간 연산 🔗

참고 🔗

SQL 조회 문법 총정리 2편 (MySQL/Oracle)
Advanced SQL Query Syntax (MySQL/Oracle)