[SQL] Subquery

jane05·2023년 11월 5일

DS19 sql subquery 공부일지 데이터분석

✏️ 데이터분석 공부

목록 보기

21/21

0️⃣ About Subquery

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.
메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
• 서브쿼리는 메인쿼리의 칼럼 사용 가능
• 메인쿼리는 서브쿼리의 칼럼 사용 불가
Subquery 사용시 주의 사항 🔥
• Subquery 는 괄호로 묶어서 사용
• 단일 행 혹은 복수 행 비교 연산자와 함께 사용가능
• subquery 에서는 order by 를 사용 X
• subquery는 반드시 alias를 지정해준다! -> 오류 방지
Subquery 종류
• 스카라 서브쿼리 (Scalar Subquery) - SELECT 절에 사용
• 인라인 뷰 (Inline View) - FROM 절에 사용
• 중첩 서브쿼리 (Nested Subquery) - WHERE 절에 사용

1️⃣ 스칼라 서브쿼리 (Scalar Subquery)

SELECT 절에서 사용하는 서브쿼리.
결과는 하나의 Column 이어야 한다.

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

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

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

2️⃣ 인라인 뷰 (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 LIKE '발생'
    GROUP BY police_station) m
WHERE c.police_station = m.police_station
    AND c.case_number = m.count;

3️⃣ 중첩 서브쿼리 (Nested Subquery)

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

1. Single Row Subquery

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

주의 사항

괄호가 없으면 에러가 발생한다.(ERROR 1065 (42000))
한 개 이상의 결과가 나타날 시 에러가 발생한다.(ERROR 1242 (21000))

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

예제)

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

2.1. Multiple Row - IN

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

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

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

SELECT host
FROM snl_show
WHERE host IN (SELECT name
                FROM celeb
                WHERE JOB_TITLE Like '%영화배우%')'

2.2 Multiple Row - EXISTS

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

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

예제) 범죄 검거 혹은 발생 건수가 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);

2.3. Multiple Row - ANY

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

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

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

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

2.4. Multiple Row - ALL

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

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

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

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

3. Multi 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='강동원');

4️⃣ 문제 풀이

oil_price 테이블에서 셀프주유의 평균가격과 SK에너지의 가장 비싼 가격을
Scalar Subquery 를 사용하여 조회
```
select max(가격), (select avg(가격) from oil_price where 셀프='Y')
from oil_price
where 상표='SK에너지';
```

oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 Inline View 를 사용하여 조회하세요.

select o.상호, o.상표, s.max_price
from oil_price o,
(select 상표, max(가격) as 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 를 사용하여 조회하세요. (refueling 테이블)

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.주유소;

jane05

데이터 분석 공부 기록

이전 포스트