상품 목록에 대표 이미지 한 장씩을 붙이는 화면이 있다고 해봅시다. 테이블은 이렇게 생겼어요.
CREATE TABLE product (
product_id NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
CREATE TABLE product_image (
image_id NUMBER PRIMARY KEY,
product_id NUMBER REFERENCES product, -- 상품 하나에 이미지 여러 장
url VARCHAR2(500),
is_main CHAR(1) -- 대표 이미지면 'Y'
);product_image가 product를 참조하니 상품 하나에 이미지가 여러 장 달립니다. 그중 하나에 is_main = 'Y'를 달아 대표 이미지로 쓰고, 목록 화면에는 그 한 장만 보여주면 되죠.
방법은 두 가지입니다.
하나는 나눠서 가져오는 것입니다. 상품 목록을 조회하고, 그 상품들의 이미지를 또 조회한 다음, 자바에서 짝을 지어 붙입니다.
List<Product> products = productMapper.findAll(); // 쿼리 1
List<Long> ids = products.stream().map(Product::getId).toList();
List<ProductImage> images = imageMapper.findMainImages(ids); // 쿼리 2
Map<Long, String> urlById = images.stream()
.collect(Collectors.toMap(ProductImage::getProductId, ProductImage::getUrl));
products.forEach(p -> p.setMainImage(urlById.get(p.getId()))); // 조립됩니다. 다만 목록 화면 하나 그리자고 DB를 두 번 다녀와야 하고, 붙이는 코드도 여섯 줄 따로 있어야 하죠.
다른 하나는 SQL 한 문장으로 끝내는 것입니다.
SELECT p.name,
(SELECT i.url
FROM product_image i
WHERE i.product_id = p.product_id
AND i.is_main = 'Y') AS main_image
FROM product p;SELECT절 한복판에 괄호로 SELECT가 하나 더 들어갔습니다. 이렇게 쿼리 안에 들어간 쿼리를 서브쿼리라고 합니다. 상품 한 행마다 저 안쪽 쿼리가 돌아서 대표 이미지 주소 하나를 가져다 칸을 채워요. 왕복도 한 번이고 조립 코드도 없습니다.
이 글은 이 서브쿼리라는 것을 처음부터 짚습니다. 무엇이고, 왜 있고, 어느 자리에 무엇을 놓아야 하는지요.
서브쿼리는 애초에 왜 쓰는 걸까요
자리 이야기를 하기 전에, 서브쿼리가 무엇이고 왜 있는지부터 짚고 가겠습니다.
앞에서 한 줄로 이름만 붙이고 넘어갔죠. 다시 적으면 서브쿼리는 다른 SQL 문 안에 들어간 SELECT 문입니다. 정의는 이게 전부예요.
문제는 “그래서 왜 안에 넣나”죠. Oracle 문서가 한 줄로 답합니다.
A subquery answers multiple-part questions.
두 단계로 물어야 답이 나오는 질문에 쓴다는 뜻입니다. 문서가 든 예가 정확히 이겁니다. “Taylor와 같은 부서에서 일하는 사람은 누구인가.”
이 질문은 한 번에 못 답합니다. Taylor가 어느 부서인지부터 알아야 하니까요. 서브쿼리 없이 하면 이렇게 됩니다.
-- 1단계: Taylor의 부서를 알아낸다
SELECT department_id FROM employees WHERE last_name = 'Taylor';
--> 80
-- 2단계: 사람이 80을 눈으로 읽어서 옮겨 적는다
SELECT last_name FROM employees WHERE department_id = 80;돌아가긴 합니다. 그런데 80이라는 값이 사람 손을 거칩니다. Taylor가 부서를 옮기면 이 쿼리는 조용히 틀린 답을 내놓아요. 코드에 80을 박아두면 더 심하고요.
서브쿼리는 그 중간값을 사람 손에서 빼서 쿼리 안에 넣습니다.
SELECT last_name
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE last_name = 'Taylor');이제 부서 번호는 실행할 때마다 새로 구해집니다.
한 문장으로 묶으면 따라오는 것
두 단계를 한 문장으로 합치면 편의 말고도 얻는 게 하나 더 있습니다. 한 시점의 데이터로 답이 나온다는 것입니다.
Oracle은 문장 수준 읽기 일관성(statement-level read consistency)을 항상 보장합니다. 문서 표현으로는, 하나의 질의가 돌려주는 데이터는 한 시점의 커밋된 데이터임이 보장됩니다.
여기서 커밋은 바꾼 내용을 확정해서 다른 사람도 보게 만드는 것을 말합니다. 확정 전에는 나만 보이고, 커밋해야 남들 눈에 보여요. 그러니 “한 시점의 커밋된 데이터”란 내 쿼리가 시작한 그 순간까지 확정된 것만 보인다는 뜻입니다. 그 뒤에 남이 무엇을 확정하든 내 쿼리 결과는 안 흔들립니다.
위 두 단계짜리 버전에서는 이게 안 됩니다. 1단계와 2단계는 서로 다른 문장이라 각자 자기 시점을 봅니다. 그 사이에 Taylor가 부서를 옮기고 커밋하면, 1단계는 옛 부서를 주고 2단계는 새 부서 사람들을 훑는 어긋난 상태가 나옵니다.
쿼리를 쪼개면 이 보장을 잃습니다. 나눠서 가져오는 방법이 편해 보여도, 두 문장 사이의 틈은 이렇게 열려 있습니다.
정리하면 서브쿼리를 쓰는 이유는 둘입니다. 중간 결과를 사람 손에서 빼는 것, 그리고 한 시점의 데이터로 판단하는 것입니다.
자리가 반환값을 정합니다
서브쿼리를 “스칼라 서브쿼리, 인라인뷰, 중첩 서브쿼리” 세 종류로 외우면 잘 안 붙습니다. 이름이 세 개라서 종류가 세 개인 게 아니거든요. 놓이는 자리가 세 곳이고, 자리마다 요구하는 게 다른 것입니다.
| 자리 | 그 자리가 요구하는 것 | Oracle이 부르는 이름 |
|---|---|---|
SELECT절 |
행마다 값 하나 | 스칼라 서브쿼리 표현식 |
FROM절 |
집합, 즉 테이블 하나 | 인라인뷰(inline view) |
WHERE절 |
참인지 거짓인지 | 중첩 서브쿼리(nested subquery) |
이름 두 개는 Oracle 공식 문서에 그대로 있는 표현입니다. FROM절에 대해서는 “A subquery in the FROM clause of a SELECT statement is also called an inline view”, WHERE절에 대해서는 “A subquery in the WHERE clause of a SELECT statement is also called a nested subquery”라고 적혀 있어요.
이제 자리마다 하나씩 봅시다. 여기서부터는 Oracle의 HR 샘플 스키마를 씁니다. Oracle이 연습용으로 배포하는 테이블 묶음이라 그대로 돌려보실 수 있어요. employees(사원), departments(부서), job_history(사원의 지난 직무 이력) 세 테이블만 알면 따라오실 수 있습니다.
SELECT절은 값 하나를 요구합니다
SELECT절의 각 항목은 결과 표의 칸 하나를 채웁니다. 칸 하나에는 값이 하나 들어가야죠. 그래서 여기 놓인 서브쿼리는 값 하나로 줄어들어야 합니다.
Oracle 문서의 정의는 이렇습니다. 스칼라 서브쿼리 표현식은 한 행에서 한 컬럼 값을 돌려주는 서브쿼리이고, 그 값이 곧 표현식의 값이 됩니다.
그리고 행 수가 어긋났을 때의 처리가 위아래로 다릅니다. 이게 처음 보면 놀라운 지점이에요.
- 서브쿼리가 0행을 돌려주면, 표현식의 값은
NULL이 된다 - 서브쿼리가 2행 이상을 돌려주면, 에러가 난다
부족한 건 조용히 NULL로 넘어가고, 넘치는 것만 에러입니다.
2번이 앞의 대표 이미지 쿼리에서 실제로 터집니다. 어떤 상품에 is_main = 'Y'인 이미지가 두 장 들어가면요.
ORA-01427: single-row subquery returns more than one row“한 행만 돌려줘야 할 서브쿼리가 여러 행을 돌려줬다“는 뜻입니다. 쿼리는 배포 이후 한 글자도 안 바뀌었어요. 바뀐 건 데이터뿐입니다. 이 쿼리를 어떻게 고칠지는 글 끝에서 다룹니다.
1번이 왜 중요하냐면, 부서가 없는 사원을 넣어 보면 알 수 있습니다.
SELECT e.last_name,
(SELECT d.department_name
FROM departments d
WHERE d.department_id = e.department_id) AS dept_name
FROM employees e
WHERE e.department_id IS NULL;department_id가 NULL인 사원은 조건에 맞는 부서가 없으니 서브쿼리가 0행입니다. 그런데 에러 없이 dept_name만 NULL인 행이 그냥 나옵니다.
값이 없어도 그 사원 행 자체는 사라지지 않아요. 값이 없으면 행은 그대로 남고 칸만 비워집니다. 짝을 못 찾아도 행을 지우지 않고 빈 칸으로 남긴다는 것, 이게 이 자리의 성격입니다.
FROM절은 집합을 요구합니다
FROM절에는 원래 테이블 이름이 옵니다. 그러니 그 자리에 놓인 서브쿼리는 테이블처럼 생긴 것, 즉 여러 행과 여러 컬럼을 가진 집합이어야 합니다.
이걸 인라인뷰라고 불러요. 뷰(view)란 자주 쓰는 SELECT문에 이름을 붙여 테이블처럼 쓰게 만든 것인데, 그걸 따로 만들어두지 않고 쿼리 안에 그 자리에서 박아 넣었다는 뜻으로 인라인뷰입니다.
값 하나로 줄어들 필요가 없다는 게 핵심입니다. 대표 이미지가 두 장이라 SELECT절에서 터졌던 그 서브쿼리도, 이 자리로 오면 에러가 나지 않아요. 요구가 다르니까요. 대신 그 상품이 결과에 두 줄로 나옵니다. 집합을 요구하는 자리는 여러 행을 그대로 받아들이니까요.
WHERE절은 참인지 거짓인지를 요구합니다
WHERE절은 행 하나하나를 두고 “남길까 버릴까”를 판단하는 자리입니다. 그러니 여기 놓인 서브쿼리는 조건을 만드는 재료가 되어야 합니다. Oracle은 이걸 중첩 서브쿼리라고 부릅니다.
같은 자리인데도 쓰는 방식이 갈립니다.
-- (1) 값 하나로 줄여서 비교
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- (2) 값 목록에 들어있는지
SELECT last_name
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700);
-- (3) 있기만 하면 되는지
SELECT e.last_name
FROM employees e
WHERE EXISTS (SELECT 1
FROM job_history h
WHERE h.employee_id = e.employee_id);(1)은 사실 스칼라 서브쿼리입니다. AVG(salary)는 언제나 1행 1컬럼이니까요. 자리가 WHERE절일 뿐, 값 하나로 줄어들어야 한다는 요구는 똑같습니다.
(3)의 SELECT 1이 눈에 걸리실 수 있습니다. EXISTS는 행이 있느냐 없느냐만 보기 때문에, 무엇을 고르는지는 상관이 없어요. 그래서 실제로 값을 읽지 않겠다는 뜻으로 상수를 씁니다.
그리고 (3)에는 (1), (2)에 없는 게 하나 더 있습니다. 서브쿼리 안쪽에서 바깥 쿼리의 컬럼인 e.employee_id를 쓰고 있어요. 이렇게 안쪽이 바깥을 참조하는 것을 상관 서브쿼리(correlated subquery)라고 부릅니다. 바깥 행이 정해져야 안쪽을 판단할 수 있으니, 두 쿼리가 서로 엮여 있는 셈이죠.
기억해 두세요. 바깥을 참조하는 이 재주는 WHERE절이라서 되는 것입니다. FROM절은 사정이 다릅니다.
셋 중 무엇을 고르나
같은 자리에 셋이 다 놓이니 헷갈립니다. 기준은 “안쪽에서 무엇을 꺼내야 하는가“입니다.
값 하나로 줄어드는 게 확실하면 (1) 스칼라 비교입니다. AVG(평균), MAX(최댓값), COUNT(개수)처럼 여러 행을 값 하나로 줄이는 함수를 집계함수라고 하는데, 이런 함수는 GROUP BY가 없으면 언제나 1행이라 안전해요. 반대로 집계 없이 그냥 SELECT하는 거라면 위험합니다. 1행이 보장되지 않으면 ORA-01427로 터지거든요. 맨 앞 대표 이미지 쿼리와 같은 함정이 여기서도 그대로 나옵니다.
여러 값 중 하나와 맞으면 되면 (2) IN입니다. 목록에 몇 행이 오든 안전하고, 읽기도 제일 쉽습니다. 다만 비교할 컬럼이 하나일 때 편합니다. 그리고 부정형으로 뒤집을 때 NULL 함정이 있는데, 바로 다음 절에서 다룹니다.
존재 여부만 알면 되면 (3) EXISTS입니다. 값을 꺼내지 않으니 안쪽이 몇 행이든, 어떤 컬럼이든 상관없습니다. 그래서 조건이 복잡할 때 IN보다 잘 버팁니다. 두 컬럼을 동시에 맞춰야 한다거나, 안쪽에서 날짜 범위 같은 추가 조건을 걸어야 할 때요. 대신 상관 서브쿼리라 조인 조건을 손으로 써야 하고, 그걸 잘못 쓰면 조용히 다른 결과가 나옵니다. IN은 그 조건이 = 하나로 고정이라 틀릴 여지가 적어요.
정리하면 값이 필요하면 (1), 목록 대조면 (2), 존재 확인이면 (3)입니다. 그리고 (2)와 (3) 사이에서 고민된다면 부정형인지를 보세요. 다음 절이 그 이야기입니다.
그래서 대표 이미지 쿼리는 어떻게 고치나
처음 그 쿼리로 돌아옵시다. 상품 목록에 대표 이미지를 붙이다 ORA-01427로 터졌던 것이요.
이제 왜 터졌는지는 분명합니다. SELECT절은 값 하나를 요구하는데 서브쿼리가 그걸 보장하지 못했습니다. 그러면 자리를 FROM절로 옮기는 게 답일까요.
아닙니다. 옮기면 이렇게 되는데요.
SELECT p.name, img.url AS main_image
FROM product p
JOIN (SELECT product_id, url
FROM product_image
WHERE is_main = 'Y') img
ON img.product_id = p.product_id;에러는 안 나지만 대표 이미지가 두 장인 상품이 목록에 두 줄로 뜹니다. 이게 에러보다 나쁠 수도 있어요. 터지면 알기라도 하는데 이건 아무도 모르고 지나갑니다.
진짜 선택지는 셋입니다.
첫째, DB가 1행을 보장하게 만듭니다.
애초에 왜 두 장이 들어갔는지 보면 답이 나옵니다. “대표 이미지는 상품당 한 장”이라는 건 우리 머릿속의 약속이었지 DB의 제약이 아니었습니다. is_main에 'Y'를 두 번 넣는 걸 막는 장치가 없으니, 관리자 화면에서 대표를 바꾸다 실수하거나 데이터를 옮기다 한 번 어긋나면 그대로 들어갑니다. 쿼리는 처음부터 틀려 있었고, 데이터가 몇 달 동안 그걸 가려주고 있었을 뿐이에요.
그러면 약속을 제약으로 바꾸면 됩니다. 유니크 제약은 그 값이 테이블 안에서 중복되면 안 된다고 DB에 걸어두는 규칙입니다. 다만 우리에게 필요한 건 이미지 전체가 유일한 게 아니라 대표인 것끼리만 상품당 하나예요. 이렇게 조건이 붙은 것을 Oracle 문서는 조건부 유니크 제약이라 부르고, 함수 기반 유니크 인덱스로 걸 수 있다고 안내합니다.
CREATE UNIQUE INDEX ux_product_main_image
ON product_image (CASE WHEN is_main = 'Y' THEN product_id END);is_main이 'Y'인 행만 product_id 값을 갖고, 나머지는 NULL이 됩니다. 그리고 Oracle은 키 컬럼이 전부 NULL인 행을 인덱스에 넣지 않습니다. 그래서 대표가 아닌 이미지들은 몇 장이든 상관없고, 대표만 상품당 하나로 강제됩니다.
26ai Free에서 실제로 돌려 보니 그대로였습니다. 대표가 아닌 이미지는 같은 상품에 여러 장 들어가고, 대표를 두 장째 넣으려 하면 이렇게 막힙니다.
ORA-00001: unique constraint (UX_MAIN_IMG) violated약속이 제약이 된 겁니다. 이제 잘못된 데이터가 아예 안 들어오니 쿼리도 안전해집니다.
트레이드오프는 분명합니다. 인덱스를 만드는 시점에 이미 중복이 있으면 생성 자체가 실패합니다. 실제로 대표가 두 장인 상태에서 만들어 보면 ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found가 납니다. 그러니 기존 데이터를 먼저 정리해야 하고, 운영 DB에 스키마를 변경할 권한과 절차가 필요합니다. 남의 시스템에 붙어 있거나 스키마를 못 건드리는 상황이면 못 씁니다.
둘째, 쿼리에서 1행으로 줄입니다. 스키마를 못 건드릴 때의 현실적인 선택입니다.
SELECT p.name,
(SELECT MAX(i.url)
FROM product_image i
WHERE i.product_id = p.product_id
AND i.is_main = 'Y') AS main_image
FROM product p;MAX는 여러 행이 와도 언제나 1행을 돌려주니 ORA-01427이 사라집니다. 대신 두 장 중 어느 것이 나올지는 URL 문자열 순서라는 의미 없는 기준으로 정해집니다. 화면은 안 터지지만 데이터가 잘못됐다는 사실은 그대로 덮입니다. 급한 불을 끄는 용도로는 맞고, 여기서 멈추면 안 되는 조치예요.
셋째, 정말 여러 장을 보여줘야 하는 화면이라면 애초에 값 하나를 붙이는 문제가 아닙니다. 그때는 FROM절 인라인뷰로 조인해서 행이 늘어나는 게 맞고, 늘어난 행을 애플리케이션에서 상품 단위로 묶으면 됩니다.
세 선택지를 가르는 질문은 하나입니다. “1행이라는 게 데이터의 사실인가, 아니면 내 기대인가.” 사실이면 첫째로 못 박고, 기대일 뿐이면 둘째로 버티다 첫째로 갑니다. 애초에 1행이 아니어도 되는 화면이면 셋째입니다.
다음에 서브쿼리를 쓸 때
판단 기준은 “내가 붙이려는 게 값 하나인가, 집합인가, 조건인가” 하나입니다.
행마다 값 하나를 덧붙이는 거라면 SELECT절의 스칼라 서브쿼리입니다. 사원 행에 부서명 하나를 붙이는 경우죠. 조건이 붙습니다. 그 서브쿼리가 어떤 입력에도 1행 이하를 보장해야 합니다. 그 보장이 기본 키나 유니크 제약에서 나오는지, 아니면 “그럴 것 같아서”인지를 구분하세요. 후자면 대표 이미지 쿼리와 같은 길을 갑니다. 값이 없을 때 행이 사라지지 않고 NULL 칸이 남는 게 원하는 동작인지도 함께 확인해야 합니다.
테이블처럼 다뤄야 할 집합이라면 FROM절의 인라인뷰입니다. 여러 행이 그대로 필요하거나, 안쪽에서 한 번 가공한 결과를 바깥에서 테이블처럼 쓰고 싶을 때죠.
행을 남길지 버릴지만 정하는 거라면 WHERE절입니다. 여기서 다시 갈립니다. 값을 비교해야 하면 IN, 존재 여부만 보면 EXISTS입니다. 그리고 부정형에서는 NULL이 끼어들 여지가 있으면 NOT IN 대신 NOT EXISTS를 봅니다.
처음의 대표 이미지 쿼리로 다시 돌아가 보면, 그 쿼리가 틀렸던 이유도 결국 하나였습니다. SELECT절에 놓았으면서 그 자리가 요구하는 1행을 보장하지 않았던 것. 자리를 옮겨서 될 일이 아니라, 요구를 채우거나 자리를 제대로 고르거나 둘 중 하나였어요.
다음에 서브쿼리를 쓸 때 “이 자리는 나에게 무엇을 요구하는가” 한 번만 물어보시면 됩니다. 이 글의 내용은 전부 거기서 나옵니다.
규칙을 지켰는데도 어긋날 때
여기까지가 자리별 규칙입니다. 그런데 규칙을 지켜 썼는데도 결과가 어긋나는 자리가 있습니다.
NOT IN은 서브쿼리 결과에 NULL이 하나만 섞여도 에러 없이 0행을 내놓습니다. 조건에 맞는 게 없어서가 아니라 조건 자체가 참이 될 수 없어서요. 인라인뷰는 WHERE절에서는 되던 바깥 컬럼 참조가 안 됩니다. WITH는 “이름만 붙인 인라인뷰”가 아니라 처리 방식이 갈릴 수 있는 다른 물건이고요.
셋 다 에러가 안 나서 더 위험합니다. 결과가 조용히 틀린 채로 배포됩니다. 다음 편에서 그 자리들을 하나씩 봅니다.
확인한 환경
문서로 확인한 내용은 아래 참고의 Oracle 공식 문서를 따랐습니다. 문서만으로 판단이 안 서는 것은 직접 돌려서 확인했고, 그 환경은 이렇습니다.
- Docker의
gvenzl/oracle-free:slim이미지 Oracle AI Database 26ai Free Release 23.26.2.0.0- 몇 행짜리 테스트 테이블
참고
- Oracle Database SQL Language Reference 19c: Using Subqueries
- Oracle Database SQL Language Reference 19c: Scalar Subquery Expressions
- Oracle Database SQL Language Reference 19c: IN Condition
- Oracle Database Concepts 19c: Data Concurrency and Consistency (문장 수준 읽기 일관성)
- ORA-01427: single-row subquery returns more than one row
- Oracle Database Development Guide 19c: Using Indexes in Database Applications (함수 기반 유니크 인덱스로 조건부 유니크 제약)
- Oracle 샘플 스키마 리포: human_resources