[zerobase_데이터취업스쿨] SQL~CH17

DONGYOON KIM·2024년 1월 23일

SQL

목록 보기

9/14

CH17-9: 중첩서브쿼리(WHERE절에 쓰이는 서브쿼리)(SINGLE ROW, SINGLE COLUMN)

범죄 검거 혹은 발생 건수 중 적어도 하나 이상이 2000건 초과인 경찰서 조회

mysql> SELECT NAME FROM police_station
    -> WHERE EXISTS (SELECT CASE_NUMBER FROM crime_status
    -> WHERE CASE_NUMBER > 2000 AND NAME = CONCAT('서울',POLICE_STATION,'경찰서'));  
+------------------+
| NAME             |
+------------------+
| 서울강남경찰서   |
| 서울강서경찰서   |
| 서울관악경찰서   |
| 서울구로경찰서   |
| 서울노원경찰서   |
| 서울송파경찰서   |
| 서울영등포경찰서 |
| 서울중랑경찰서   |
+------------------+
8 rows in set (0.01 sec)

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

mysql> SELECT NAME FROM celeb
    -> WHERE NAME = ANY (SELECT HOST FROM snl_show
    -> );    
+--------+
| NAME   |
+--------+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |
+--------+
4 rows in set (0.01 sec)

서브쿼리 결과를 모두 만족하면 TRUE를 반환(ALL)

mysql> SELECT NAME FROM celeb
    -> WHERE NAME = ALL (SELECT HOST FROM snl_show
    -> WHERE AGENCY LIKE '%YG%');
+--------+
| NAME   |
+--------+
| 아이유 |
| 이미주 |
| 송강   |
| 유재석 |
+--------+
4 rows in set (0.01 sec)

CH17-10: 중첩서브쿼리(MULTIPLE COLUMN)

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

mysql> SELECT NAME, SEX, AGENCY FROM celeb
    -> WHERE SEX = (SELECT SEX FROM celeb
    -> WHERE NAME = '강동원')
    -> AND
    -> AGENCY = (SELECT AGENCY FROM celeb 
    -> WHERE NAME = '강동원');
+--------+------+----------------+
| NAME   | SEX  | AGENCY         |
+--------+------+----------------+
| 강동원 | M    | YG엔터테이먼트 |
| 차승원 | M    | YG엔터테이먼트 |
+--------+------+----------------+
2 rows in set (0.01 sec)

CH17-11: 서브쿼리 문제풀이

oil_price테이블에서 셀프주유의 평균가격과 sk에너지의 가장 비싼 가격을 scalar subquery로 조회

mysql> SELECT AVG(가격) AS 셀프평균, (SELECT MAX(가격) FROM oil_price
    -> WHERE 상표 LIKE '%SK%')  
    -> FROM oil_price
    -> WHERE 셀프 LIKE 'Y';
+-----------+----------------------------------------------------------+
| 셀프평균  | (SELECT MAX(가격) FROM oil_price
WHERE 상표 LIKE '%SK%') |
+-----------+----------------------------------------------------------+
| 1571.7978 |                                                     2611 |
+-----------+----------------------------------------------------------+
1 row in set (0.01 sec)

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

mysql> select o.상표, o.상호, m.가격 from oil_price
    -> as o, (select 상표, max(가격) as 가격 from oil_price
    -> group by 상표) as m
    -> where m.가격 = o.가격
    -> and m.상표 = o.상표;
+----------------+-------------------------------------+------+
| 상표           | 상호                                | 가격 |
+----------------+-------------------------------------+------+
| 알뜰(ex)       | 만남의광장주유소                    | 1514 |
| S-OIL          | (주)선문에너지 중앙주유소           | 2168 |
| GS칼텍스       | 서계주유소                          | 2609 |
| 자가상표       | 코끼리주유소                        | 1653 |
| 알뜰주유소     | 세화주유소                          | 1648 |
| SK에너지       | 서남주유소                          | 2611 |
| HD현대오일뱅크 | (주)제이제이네트웍스 제이제이주유소 | 2490 |
+----------------+-------------------------------------+------+
7 rows in set (0.01 sec)

MySQL 오류 메시지 ERROR 1054 (42S22): Unknown column 'o.상호' in 'field list'는 o.상호라고 지칭하는 컬럼이 쿼리를 실행하는 컨텍스트에서 인식되지 않았음을 나타냅니다. 이는 o라는 별칭(alias)이 해당 쿼리에서 정의되지 않았거나 잘못 사용되었을 때 발생합니다.

제시된 쿼리에는 oil_price 테이블에 대한 별칭 o가 명시적으로 설정되지 않았습니다. MySQL에서 테이블 별칭을 사용하려면, 해당 테이블 이름 뒤에 별칭을 지정해야 합니다. 또한, oil_price 테이블과 서브쿼리 m 사이의 조인(Join)이 명시되어 있지 않습니다.

쿼리를 수정하여 테이블 별칭을 올바르게 설정하고 조인을 명확하게 해야 합니다. 다음은 수정된 쿼리의 예시입니다:

SELECT
  o.상호,
  o.상표,
  m.가격
FROM
  oil_price AS o
JOIN
  (SELECT MAX(가격) AS 가격, 상표 FROM oil_price GROUP BY 상표) AS m
ON
  o.상표 = m.상표
AND
  o.가격 = m.가격;

위 쿼리는 다음과 같은 변경을 포함합니다:

oil_price 테이블에 o라는 별칭을 붙여서 o.상호, o.상표로 컬럼을 참조할 수 있도록 했습니다.
서브쿼리에서 상표 컬럼을 SELECT 문에 추가하여 외부 쿼리에서 m.상표로 참조할 수 있도록 했습니다.
메인 쿼리와 서브쿼리를 조인할 때 JOIN 키워드를 사용하고 조인 조건을 ON 절에 명시했습니다.
가격이 가장 높은 행을 찾기 위해 o.가격 = m.가격 조건을 추가했습니다.

이제 이 쿼리는 각 상표에 대한 최대 가격을 가지는 oil_price 테이블의 상호와 상표를 반환합니다.

SELECT 
  o.상호, 
  o.상표, 
  o.가격 
FROM 
  oil_price o, 
  (SELECT MAX(가격) AS 가격, 상표 FROM oil_price GROUP BY 상표) AS m 
WHERE 
  o.상표 = m.상표 
  AND o.가격 = m.가격;

위 쿼리에서는 콤마를 사용하여 oil_price 테이블(별칭 o)과 서브쿼리(별칭 m)을 묵시적으로 조인하고 있습니다. 하지만 이 방식은 ANSI SQL 표준에서 권장하지 않으며, 특히 복잡한 쿼리에서는 의도하지 않은 카테시안 곱(Cartesian Product)을 생성할 수 있어 주의가 필요합니다.

JOIN을 사용하는 방식은 더 명확하게 의도를 표현할 수 있고, 최신 SQL 코드 작성에 있어 권장되는 접근법입니다. 또한 JOIN을 사용하면 쿼리의 가독성이 향상되고 디버깅이 더 쉬워집니다. JOIN 구문을 사용하면 쿼리가 더 명시적이고, 데이터베이스 엔진의 최적화 도구가 조인을 더 효율적으로 처리할 수 있게 도와줄 수 있습니다.

그러니 가능하면 명시적 JOIN 구문을 사용하는 것이 좋습니다.

네, 맞습니다. 서브쿼리에서 MAX(가격) 칼럼에 가격이라는 별칭을 부여해야 메인 쿼리에서 m.가격으로 해당 값을 참조할 수 있습니다. 서브쿼리에서 별칭을 지정하는 것은 결과 집합의 칼럼 이름을 정의하는 방법으로, 메인 쿼리에서는 이 별칭을 사용하여 해당 칼럼을 참조할 수 있게 됩니다.

mysql> select o.상표, o.상호, m.가격 from oil_price as o
    -> join (select max(가격) as 가격, 상표 from oil_price group by 상표) as m
    -> on o.상표 = m.상표 and o.가격 = m.가격;
+----------------+-------------------------------------+------+
| 상표           | 상호                                | 가격 |
+----------------+-------------------------------------+------+
| 알뜰(ex)       | 만남의광장주유소                    | 1514 |
| S-OIL          | (주)선문에너지 중앙주유소           | 2168 |
| GS칼텍스       | 서계주유소                          | 2609 |
| 자가상표       | 코끼리주유소                        | 1653 |
| 알뜰주유소     | 세화주유소                          | 1648 |
| SK에너지       | 서남주유소                          | 2611 |
| HD현대오일뱅크 | (주)제이제이네트웍스 제이제이주유소 | 2490 |
+----------------+-------------------------------------+------+
7 rows in set (0.01 sec)

평균가격보다 높은 주유소 상호와 가격을 Nested Subquery 이용하여 조회

mysql> select 상호, 가격 from oil_price
    -> where 가격 > (select avg(가격) from oil_price)
    -> limit 10;
+--------------------------------+------+
| 상호                           | 가격 |
+--------------------------------+------+
| 방아다리주유소                 | 1665 |
| 명일주유소                     | 1665 |
| (주)퍼스트오일 코알라주유소    | 1690 |
| sk해뜨는주유소                 | 1699 |
| (주)소모 성내주유소            | 1708 |
| 지에스칼텍스㈜ 동서울주유소    | 1711 |
| 주)지유에너지직영 오렌지주유소 | 1754 |
| (주)삼표에너지 고덕주유소      | 1799 |
| 천호현대주유소                 | 1843 |
| 광성주유소                     | 1968 |
+--------------------------------+------+
10 rows in set (0.01 sec)