2021-12-10 16:42 작성

My SQL basic 메모

Table of contents

Subquery 도입

MySQL에서 subquery는 SELECT, INSERT, UPDATE 혹은 DELETE와 같은 다른 쿼리 안에 내장된 쿼리를 의미한다. 또한, subquery는 다른 subquery 안에 내장될 수 있다.

MySQL subquery가 내부 쿼리(inner query)라고 명명되었다면, subquery를 포함하는 특정 쿼리는 바깥 쿼리(outer query)로 명명된다. 하나의 subquery는 expression이 사용된 어느 곳에서나 사용 가능하며 이 때 괄호로 닫혀 있어야만 한다.

예를 들어 다음의 쿼리는 USA에 위치한 offices에서 일하는 employees들을 반환하는 subquery를 사용한다.

SELECT
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

output:

lastName firstName
Murphy Diane
Patterson Mary
Firrelli Jeff
Bow Anthony
Jennings Leslie
Thompson Leslie
Firrelli Julie
Patterson Steve
Tseng Foon Yue
Vanauf George

위 예시에서:

  • Subquery는 USA에 위치한 모든 offices의 office codes를 반환한다.
  • Outer query는 subquery에서 반환되는 result set의 office codes 안에 offices의 값이 존재하는지 확인한다. 그리고 값이 존재할 경우, 해당 offices에서 근무하는 모든 employees의 firstName과 lastName을 선택한다.


쿼리를 실행할 때, MySQL은 subquery를 먼저 평가하고 그 결과를 outer query에서 사용한다.



WHERE 절에서 MySQL subquery 사용하기

증명을 위해 아래의 payments 샘플 table을 사용할 것이다.


비교 연산자와 MySQL subquery

WHERE 절에서 expression과 함께 사용된 subquery에서 반환된 하나의 값을 비교 연산자(=, >, <)를 이용해 비교할 수 있다.

예를 들어, 다음의 쿼리는 가장 많은 payment를 한 customer을 반환하는 쿼리다:

SELECT
    customerNumber,
    checkNumber,
    amount
FROM
    payments
WHERE
    amount = (SELECT MAX(amount) FROM payments);

output:

customerNumber checkNumber amount
141 JE105477 120166.58

= 연산자 외에도 >, >=, < 그리고 <=와 같은 연산자를 사용할 수 있다.

예를 들어, subquery를 사용하여 평균보다 많은 payments를 한 customers를 찾을 수 있다.

SELECT
    customerNumber,
    checkNumber,
    amount
FROM
    payments
WHERE
    amount > (SELECT
            AVG(amount)
        FROM
            payments);

output:

customerNumber checkNumber amount
112 HQ55022 32641.98
112 ND748579 33347.88
114 GG31455 45864.03
114 MA765515 82261.22
114 NR27552 44894.74
119 LN373447 47924.19
119 NG94694 49523.67

…

위 예시에서:

  • 먼저, subquery를 이용해 평균 payment를 얻는다.
  • 그리고 outer query에서 평균 payment보다 많은 payments를 선택한다.


MySQL subquery와 IN 그리고 NOT IN 연산자

만약 subquery가 하나 이상의 값을 반환하면 WHERE 절에서 IN 혹은 NOT IN 연산자를 사용할 수 있다.

다음의 customers와 orders tables를 보자:


예를 들어, orders에 없는 customers를 찾기 위해 subquery와 함께 NOT IN 연산자를 사용할 수 있다:

SELECT
    customerName
FROM
    customers
WHERE
    customerNumber NOT IN (SELECT DISTINCT
            customerNumber
        FROM
            orders);

output: 어떤 물건도 주문하지 않은 customers의 이름만 선택한다.

customerName
Havel & Zbyszek Co
American Souvenirs Inc
Porto Imports Co.
Asian Shopping Network, Co
Natürlich Autos
ANG Resellers
Messner Shopping Network

…



FROM 절에서의 MySQL subquery

FROM 절에서 subquery를 사용할 때, subquery에서 반환된 result set은 일시적 table로 사용된다. 이 table은 파생 table 혹은 형체화된(materialized) subquery로서 참조된다.

다음의 subquery는 판매 주문에서 maixmum, minimum 그리고 avaerage 수량을 찾는다:

이해를 돕기 위해 orderdetails의 자료가 어떤 구조인지 먼저 살펴보면:

SELECT * FROM orderdetails;

output: 따라서 아래 예시의 COUNT(orderNumber) AS items는 주문 상품의 종류를 말한다.

orderNumber productCode quantityOrdered priceEach orderLineNumber
10100 S18_1749 30 136.00 3
10100 S18_2248 50 55.09 2
10100 S18_4409 22 75.46 4
10100 S24_3969 49 35.29 1
10101 S18_2325 25 108.06 4
10101 S18_2795 26 167.06 1

…

SELECT
    MAX(items),
    MIN(items),
    FLOOR(AVG(items))
FROM
    (SELECT
        orderNumber, COUNT(orderNumber) AS items
    FROM
        orderdetails
    GROUP BY orderNumber) AS lineitems;

output: 가장 다양한 품목을 주문한 것은 18 종류 품목이 되고, 가장 최소 종류의 품목을 주문한 것은 1 종류 품목 그리고 평균적으로 9 종류의 품목을 주문했다고 해석할 수 있다.

MAX(items) MIN(items) FLOOR(AVG(items))
18 1 9



상관관계가 있는(correlated) subquery

이전 예시에서 subquery가 독립적이라는 것을 눈치챘을 것이다. 즉, subquery를 단독 쿼리해도 실행할 수 있다는 말이다.

단독 subquery와 달리, 상관관계가 있는 subquery는 outer query의 데이터를 사용한다. 다시 말해, 상관관계가 있는 subquery는 outer query에 의존한다. 상관관계가 있는 subquery는 outer query의 각각의 row에 적어도 한 번은 평가된다.

다음의 products table을 보자:


다음 예시는 각각의 product line에서 매입 가격이 평균 매입 가격보다 높은 products를 선택하는데 있어 상관관계가 있는 subquery를 사용하는 예시다.

SELECT
    productname,
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline);

output:

productname buyprice
1952 Alpine Renault 1300 98.58
1996 Moto Guzzi 1100i 68.99
2003 Harley-Davidson Eagle Drag Bike 91.02
1972 Alfa Romeo GTA 85.68
1962 LanciaA Delta 16V 103.42
1968 Ford Mustang 95.34
2001 Ferrari Enzo 95.59
1958 Setra Bus 77.90

…

위의 예시에서, outer query와 상관관계가 있는 subquery는 같은 products table을 참조한다. 그래서 outer query에 있는 products에는 table alias p1을 사용해야 한다.

자주 사용하는 subquery와 달리 이런 식의 상관관계가 존재하는 subquery는 독립적으로 실행할 수 없다. 만약 실행한다면, MySQL은 p1 table의 의미를 알아듣지 못할 것이고 에러가 발생할 것이다.

SELECT 
    AVG(buyprice)
FROM
    products
WHERE
    productline = p1.productline;

p1 table에서 각각의 row 마다 WHERE 절 이하의 전체 평균에 대한 조건을 실행하기 위해서는 상관관계가 있는 subquery가 적어도 한 번은 실행되어야 한다.

위의 예시에서 subquery는 p1 table의 productline 항목 별 평균을 산출한다.

다음을 통해 실제 데이터를 증명해보자:

SELECT
    (SELECT
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)
FROM products p1;

output: 각 항목의 평균값이 각 row마다 적용되어 이 값을 outer query의 buyprice와 비교한다. 만약, WHERE productline = p1.productline이 존재하지 않으면 항목별이 아닌 전체 평균값으로 계산하게 된다.

 
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
64.446316
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
50.685385
49.629167
49.629167
49.629167
49.629167
49.629167
49.629167
49.629167
49.629167
49.629167
49.629167
49.629167
49.629167
47.007778
47.007778
47.007778



MySQL subquery와 EXISTS 그리고 NOT EXISTS

Subquery가 EXISTS 혹은 NOT EXISTS 연산자와 사용될 때, subquery는 TRUE 혹은 FALSE를 반환한다.

다음의 쿼리는 EXISTS 연산자를 사용자가 포함해 형태를 실제적으로 보여준다:

SELECT 
    *
FROM
    table_name
WHERE
    EXISTS( subquery );

위의 쿼리에서 만약 subquery가 어떤 rows라도 반환하면 EXISTS subquery는 TRUE를 반환하고 그렇지 않으면 FALSE를 반환한다.

EXISTS와 NOT EXISTS는 종종 상과관계가 있는 subqueries와 사용된다.

다음의 orders와 orderdetails 샘플 tables를 보자:


다음의 쿼리는 total 값이 60K를 초과하는 sales orders를 찾는다:

SELECT
    orderNumber,
    SUM(priceEach * quantityOrdered) total
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;

output:

orderNumber total
10165 67392.85
10310 61234.67
10287 61402.00

상관관계가 있는 subquery를 EXISTS와 함께 사용함으로써 더 복잡한 쿼리를 만들 수 있다. 예를 들어, total value가 60K를 초과하는 sales order를 적어도 하나 이상 달성한 customers를 찾는데 사용할 수 있다.

SELECT
    customerNumber,
    customerName
FROM
    customers
WHERE
    EXISTS( SELECT
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        WHERE
            customerNumber = customers.customerNumber
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);

output:

customerNumber customerName
148 Dragon Souveniers, Ltd.
259 Toms Spezialitäten, Ltd
298 Vida Sport, Ltd