[SQL] 17-SQL Subquery

김지현·2024년 6월 17일

sql

SQL

목록 보기

16/16

📂 서브쿼리(Subquery)

하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
메인쿼리가 서브쿼리를 포함하는 종속적인 관계
✔ 서브쿼리는 메인쿼리의 컬럼 사용 가능
✔ 메인쿼리는 서브쿼리의 컬럼 사용 불가

Subquery 사용 시 주의사항

Subquery는 괄호로 묶어서 사용
단일 행 or 복수 행 비교 연산자와 함께 사용 가능
subquery에서는 order by 사용 x

Subquery 종류

스칼라 서브쿼리 (Scalar Subquery) - SELECT절에 사용
인라인 뷰 (Inline View) - FROM절에 사용
중첩 서브쿼리 (Nested Subquery) - WHERE절에 사용

📍 스칼라 서브쿼리 (Scalar Subquery)

SELECT절에서 사용하는 서브쿼리, 결과는 하나의 column이어야 함

SELECT column1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;

(예제) 서울 은평 경찰서 강도 검거 건수와 서울시 경찰서 전체 평균 강도 검거 건수를 조회

SELECT case_number,
	(SELECT avg(case_number) 
     FROM crime_status 
     WHERE crime_type = '강도' and status_type LIKE '검거') avg
FROM crime_status
WHERE police_station = '은평' and crime_type like '강도' and status_type = '검거';

📍 인라인 뷰 (Inline View)

FROM절에 사용하는 서브쿼리, 메인쿼리에서는 인라인 뷰에서 조회한 column만 사용 가능
서브쿼리 결과가 테이블 형태

SELECT a.column, b.column
FROM table1 a, (SELECT column1, column2 FROM table2) b
WHERE condition;

(예제) 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형 조회

SELECT c.police_station, c.crime_type, c.case_number
FROM crime_status c,
	(SELECT police_station, max(case_number) count
     FROM crime_status
     WHERE status_type = '발생'
     GROUP BY police_station) m
WHERE c.police_station = m.police_station and c.case_number = m.count;

📍 중첩 서브쿼리 (Nested Subquery)

WHERE절에서 사용하는 서브쿼리
✔ Single Row : 하나의 행을 검색
✔ Multiple Row : 하나 이상의 행을 검색
✔ Multiple Column : 하나 이상의 열을 검색

🔍 Single Row Subquery

서브쿼리가 비교연산자(=, >, >=, <, <=, <>, !=)와 사용되는 경우,
서브쿼리의 검색 결과는 한 개의 결과값 (두 개 이상인 경우 에러)

SELECT column_names
FROM tablename
WHERE column_name = (SELECT column_name
					 FROM tablename
                     WHERE condition)
ORDER BY column_name;

(예제)

-- 에러1. 괄호 없는 경우
SELECT name FROM celeb WHERE name = SELECT host FROM snl_show;

-- 에러2. 서브쿼리 결과가 한 개 이상의 row 반환
SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show);

-- single row 반환
SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show WHERE id = 1);

🔍 Multiple Row Subquery

📝 IN
서브쿼리 결과 중에 포함될 때

SELECT column_names
FROM tablename
WHERE column_name IN (SELECT column_name
					  FROM tablename
                      WHERE condition)
ORDER BY column_name;

(예제) SNL에 출연한 영화배우 조회

SELECT host
FROM snl_show
WHERE host IN (SELECT name FROM celeb
               WHERE job_title LIKE '%영화배우%') -- 영화배우 조회;
-- 해당 경우는 join을 사용하는 것이 더 효율적

📝 EXISTS
서브쿼리 결과에 값이 있으면 반환

SELECT column_names
FROM tablename
WHERE EXISTS (SELECT column_name
			  FROM tablename
              WHERE condition)
ORDER BY column_name;

(예제) 범죄 검거 or 발생 건수가 2000건보다 큰 경찰서 조회

SELECT name
FROM police_station p
WHERE EXISTS (SELECT police_station
			  FROM crime_status c
              WHERE p.name = c.reference AND case_number > 2000);

📝 ANY
서브쿼리 결과 중에 최소한 하나라도 만족하면 (비교연산자 사용)

SELECT column_names
FROM tablename
WHERE column_name = ANY (SELECT column_name
					 	 FROM tablename
                     	 WHERE condition)
ORDER BY column_name;

(예제) SNL에 출연한 적이 있는 연예인 이름 조회

SELECT name
FROM celeb
WHERE name = ANY (SELECT host FROM snl_show);

📝 ALL
서브쿼리 결과를 모두 만족하면 (비교연산자 사용)

SELECT column_names
FROM tablename
WHERE column_name = ALL (SELECT column_name
					 	 FROM tablename
                     	 WHERE condition)
ORDER BY column_name;

(예제) SNL에 출연한 적이 있고, snl_show id가 1인 연예인 이름조회

SELECT name
FROM celeb
WHERE name = ALL (SELECT host FROM snl_show WHERE id = 1);

🔍 Multiple Column Subquery (연관 서브쿼리)

서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
컬럼을 여러개 반환

SELECT column_names
FROM tablename a
WHERE (a.column1, a.column2, ...) IN (SELECT b.column1, b.column2, ...)
									  FROM tablename b
                                      WHERE a.column_name = b.column_name)
ORDER BY column_names;

(예제) 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회

SELECT name, sex, agency
FROM celeb
WHERE (sex, agency) IN (SELECT sex, agency FROM celeb WHERE name = '강동원');

실습

oil_price 테이블에서 셀프 주유 평균 가격과 SK에너지의 가장 비싼 가격을 scalar subquery 사용하여 조회

select avg(가격), (select max(가격) from oil_price where 상표='SK에너지') 
from oil_price where 셀프='Y';

oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 inline view를 사용하여 조회

select o.상호, o.상표, s.max_price 
from oil_price o, (select 상표, max(가격) max_price from oil_price group by 상표) s
where o.상표 = s.상표 and o.가격 = s.max_price;

평균 가격보다 높은 주유소 상호와 가격을 nested subquery를 사용하여 조회

select 상호, 가격 from oil_price
where 가격 > (select avg(가격) from oil_price);

3에서 조회한 주유소에서 주유한 연예인의 이름과 주유소, 주유일을 nested subquery를 사용하여 조회

select 이름, 주유소, 주유일 from refueling
where 주유소 in (select 상호 from oil_price where 가격 > (select avg(가격) from oil_price));

refueling 테이블과 oil_price 테이블에서 10만원 이상 주유한 연예인 이름, 상호, 상표, 주유 금액, 가격을 inline view를 사용하여 조회

select r.이름, o.상호, o.상표, r.금액, o.가격
from oil_price o, (select 이름, 주유소, 금액 from refueling where 금액 >= 100000) r
where o.상호 = r.주유소;

김지현

이전 포스트