LabEx
로그인무료 가입

MySQL 하위 쿼리 및 중첩 연산

MySQLBeginner
지금 연습하기

소개

이 랩에서는 MySQL 하위 쿼리 (subqueries) 와 중첩 연산 (nested operations) 의 강력함을 탐구합니다. 주요 초점은 WHERE 절 내에서 하위 쿼리를 사용하여 다른 테이블 또는 동일한 테이블에서 파생된 조건에 따라 데이터를 필터링하는 것입니다.

MySQL 서버에 연결하고, 데이터베이스와 테이블 (customers 및 orders) 을 생성한 다음, 특정 값을 초과하는 총 주문 금액을 가진 고객을 식별하기 위해 하위 쿼리를 활용하는 SQL 쿼리를 구성하는 방법을 배우게 됩니다. 이 랩에서는 또한 하위 쿼리와 함께 EXISTS를 사용하고, 상관 하위 쿼리 (correlated subqueries) 를 테스트하며, 하위 쿼리 성능을 비교하는 방법을 다룹니다.

WHERE 절에 하위 쿼리 작성

이 단계에서는 SQL 문의 WHERE 절 내에서 하위 쿼리를 사용하는 방법을 배우게 됩니다. 하위 쿼리는 다른 쿼리 안에 중첩된 쿼리입니다. 이는 다른 테이블 또는 동일한 테이블에서 파생된 조건을 기반으로 데이터를 검색하는 강력한 도구입니다.

하위 쿼리 이해하기

하위 쿼리 (또는 내부 쿼리) 는 더 큰 쿼리 안에 중첩된 SQL 쿼리입니다. 하위 쿼리가 먼저 실행되고, 그 결과는 외부 쿼리에서 사용됩니다. 하위 쿼리는 WHERE, SELECT, FROM, HAVING 절에 나타날 수 있습니다.

WHERE 절에서 하위 쿼리는 일반적으로 조건에 따라 외부 쿼리의 결과를 필터링하는 데 사용됩니다. 하위 쿼리는 외부 쿼리가 비교를 위해 사용하는 단일 값 또는 값 집합을 반환합니다.

시나리오

customers와 orders라는 두 개의 테이블이 있다고 가정해 보겠습니다. customers 테이블에는 고객 정보 (예: customer_id, name, city) 가 포함되어 있고, orders 테이블에는 주문 정보 (예: order_id, customer_id, order_date, total_amount) 가 포함되어 있습니다.

총 주문 금액이 $100 를 초과하는 주문을 최소 하나 이상 한 모든 고객을 찾고 싶습니다.

단계

  1. MySQL 서버에 연결:

    터미널을 열고 다음 명령을 실행하여 root 사용자로 MySQL 서버에 연결합니다.

    sudo mysql -u root

    MySQL 프롬프트 mysql>가 표시되어야 합니다.

  2. 데이터베이스 및 테이블 생성:

    아직 데이터베이스와 테이블이 없다면 지금 생성하십시오. labdb라는 데이터베이스와 customers, orders 테이블을 생성해 보겠습니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    CREATE DATABASE IF NOT EXISTS labdb;
USE labdb;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

  3. 샘플 데이터 삽입:

    테이블에 샘플 데이터를 삽입합니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice Smith', 'New York'),
(2, 'Bob Johnson', 'Los Angeles'),
(3, 'Charlie Brown', 'Chicago'),
(4, 'David Lee', 'Houston');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-01-15', 120.00),
(2, '2023-02-20', 80.00),
(1, '2023-03-10', 150.00),
(3, '2023-04-05', 200.00),
(2, '2023-05-12', 110.00),
(4, '2023-06-18', 90.00);

  4. WHERE 절에 하위 쿼리 작성:

    이제 총 주문 금액이 $100 를 초과하는 주문을 한 고객을 찾기 위한 쿼리를 작성합니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 100);

    설명:

    • 하위 쿼리 (SELECT customer_id FROM orders WHERE total_amount > 100)는 total_amount가 100 보다 큰 orders 테이블에서 customer_id를 선택합니다.
    • 외부 쿼리 SELECT * FROM customers WHERE customer_id IN (...)는 customer_id가 하위 쿼리에서 반환된 customer_id 집합에 포함되는 customers 테이블에서 모든 열을 선택합니다.

  5. 결과 확인:

    총 주문 금액이 $100 를 초과하는 주문을 한 고객을 보여주는 다음 출력을 볼 수 있습니다.

    +-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
+-------------+-------------+-----------+
3 rows in set (0.00 sec)

하위 쿼리에 EXISTS 사용

이 단계에서는 MySQL 에서 하위 쿼리와 함께 EXISTS 연산자를 사용하는 방법을 배우게 됩니다. EXISTS 연산자는 하위 쿼리에서 행의 존재 여부를 테스트하는 데 사용됩니다. 하위 쿼리가 행을 반환하면 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다.

EXISTS 이해하기

EXISTS 연산자는 다른 테이블에 관련 데이터가 존재하는지에 따라 결과를 필터링하기 위해 SQL 문의 WHERE 절에서 자주 사용됩니다. 이는 IN 또는 JOIN 연산을 사용하는 강력한 대안이며, 특히 대규모 데이터 세트를 다룰 때 더 효율적일 수 있습니다.

IN과 달리 EXISTS는 실제로 하위 쿼리에서 데이터를 검색하지 않습니다. 단순히 행이 반환되는지 여부만 확인합니다. 따라서 일치하는 항목이 있는지 여부만 알고 싶고 실제 값은 필요 없을 때 IN보다 빠를 수 있습니다.

시나리오

이전 단계의 customers 및 orders 테이블을 계속 사용하여, 최소한 하나의 주문을 한 모든 고객을 찾아봅시다.

사전 준비

이전 단계 ("WHERE 절에 하위 쿼리 작성하기") 를 완료했으며 labdb 데이터베이스, customers 테이블 및 orders 테이블에 데이터가 채워져 있는지 확인하십시오.

단계

  1. EXISTS 를 사용한 쿼리 작성:

    최소한 하나의 주문을 한 고객을 찾기 위한 쿼리를 작성합니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

    설명:

    • 외부 쿼리 SELECT * FROM customers c는 customers 테이블에서 모든 열을 선택하며, 별칭은 c입니다.
    • WHERE EXISTS (...) 절은 하위 쿼리가 행을 반환하는지 여부를 확인합니다.
    • 하위 쿼리 SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id는 orders 테이블의 customer_id가 customers 테이블의 customer_id와 일치하는 경우, orders 테이블 (별칭 o) 에서 값 1(어떤 상수 값도 가능) 을 선택합니다.
    • EXISTS 연산자는 하위 쿼리가 최소한 하나의 행을 반환하면 TRUE를 반환하여 해당 고객이 최소한 하나의 주문을 했음을 나타냅니다.

  2. 결과 확인:

    최소한 하나의 주문을 한 모든 고객을 보여주는 다음 출력을 볼 수 있습니다.

    +-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
|           4 | David Lee   | Houston   |
+-------------+-------------+-----------+
4 rows in set (0.00 sec)

  3. 쿼리 수정 (선택 사항):

    주문을 하지 않은 고객을 찾기 위해 쿼리를 수정해 봅시다. NOT EXISTS를 사용하여 이를 수행할 수 있습니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

  4. 결과 확인:

    샘플 데이터의 모든 고객이 주문을 했으므로, 이 쿼리는 빈 결과 집합을 반환해야 합니다.

    Empty set (0.00 sec)

상관 하위 쿼리 테스트

이 단계에서는 MySQL 에서 상관 하위 쿼리에 대해 배우게 됩니다. 상관 하위 쿼리는 외부 쿼리의 열을 참조하는 하위 쿼리입니다. 이는 하위 쿼리가 외부 쿼리의 각 행에 대해 한 번씩 실행된다는 것을 의미합니다.

상관 하위 쿼리 이해하기

한 번만 실행되는 일반 하위 쿼리와 달리, 상관 하위 쿼리는 값에 대해 외부 쿼리에 의존합니다. 하위 쿼리는 외부 쿼리의 현재 행에서 값을 사용하여 결과를 결정합니다. 이는 상관 하위 쿼리를 특정 유형의 쿼리에 대해 더 강력하게 만들지만, 특히 대규모 데이터 세트의 경우 일반 하위 쿼리보다 효율성이 떨어질 수 있습니다.

시나리오

customers 및 orders 테이블을 계속 사용하여, 모든 주문의 평균 주문 금액보다 큰 금액의 주문을 한 모든 고객을 찾아봅시다.

사전 준비

이전 단계를 완료했으며 labdb 데이터베이스, customers 테이블 및 orders 테이블에 데이터가 채워져 있는지 확인하십시오.

단계

  1. 상관 하위 쿼리 작성:

    평균 주문 금액보다 큰 금액의 주문을 한 고객을 찾기 위한 쿼리를 작성합니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > (SELECT AVG(total_amount) FROM orders)
);

    설명:

    • 외부 쿼리 SELECT c.customer_id, c.name FROM customers c는 customers 테이블에서 customer_id와 name을 선택하며, 별칭은 c입니다.
    • WHERE EXISTS (...) 절은 하위 쿼리가 행을 반환하는지 여부를 확인합니다.
    • 상관 하위 쿼리 SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > (SELECT AVG(total_amount) FROM orders)는 orders 테이블 (별칭 o) 에서 값 1을 선택하며, 다음 조건을 만족합니다.
      • o.customer_id = c.customer_id: 이것이 상관 관계입니다. 하위 쿼리는 외부 쿼리의 customers 테이블에서 customer_id를 참조합니다.
      • o.total_amount > (SELECT AVG(total_amount) FROM orders): 이 조건은 주문 금액이 모든 주문의 평균 주문 금액보다 큰지 확인합니다. AVG(total_amount) 하위 쿼리는 평균 주문 금액을 얻기 위해 한 번만 실행되는 비상관 하위 쿼리입니다.

  2. 결과 확인:

    평균 주문 금액보다 큰 금액의 주문을 한 고객을 보여주는 다음 출력을 볼 수 있습니다.

    +-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           1 | Alice Smith |
|           3 | Charlie Brown |
+-------------+-------------+
2 rows in set (0.00 sec)

  3. 다른 예시: 각 고객의 최고 주문 금액 찾기

    각 고객의 ID, 이름 및 최고 주문 금액을 검색하기 위해 MySQL 프롬프트에서 다음 SQL 명령을 실행합니다. 상관 하위 쿼리는 각 고객의 최대 주문 금액을 개별적으로 계산합니다.

    SELECT c.customer_id, c.name, (
    SELECT MAX(o.total_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) AS highest_order_amount
FROM customers c;

  4. 결과 확인:

    다음 출력을 볼 수 있습니다.

    +-------------+-------------+-----------------------+
| customer_id | name        | highest_order_amount  |
+-------------+-------------+-----------------------+
|           1 | Alice Smith |                150.00 |
|           2 | Bob Johnson |                110.00 |
|           3 | Charlie Brown |                200.00 |
|           4 | David Lee   |                 90.00 |
+-------------+-------------+-----------------------+
4 rows in set (0.00 sec)

하위 쿼리 성능 비교

이 단계에서는 MySQL 에서 다양한 유형의 하위 쿼리 성능을 비교하는 방법을 배우게 됩니다. 하위 쿼리의 성능 특성을 이해하는 것은 효율적인 SQL 쿼리를 작성하는 데 중요하며, 특히 대규모 데이터 세트를 다룰 때 더욱 그렇습니다.

성능 고려 사항 이해하기

하위 쿼리의 성능은 다음과 같은 여러 요인의 영향을 받을 수 있습니다.

  • 데이터 크기: 쿼리에 관련된 테이블의 크기.
  • 하위 쿼리 유형: 하위 쿼리가 상관 하위 쿼리인지 비상관 하위 쿼리인지 여부.
  • 인덱싱: 테이블에 인덱스가 존재하고 효과적인지 여부.
  • MySQL 버전: 쿼리 최적화 기법이 다를 수 있으므로 사용 중인 특정 MySQL 버전.

시나리오

customers 및 orders 테이블을 계속 사용하여, 최소한 하나의 주문을 한 모든 고객을 찾기 위해 IN을 사용하는 하위 쿼리와 EXISTS를 사용하는 하위 쿼리의 성능을 비교해 봅시다.

사전 준비

이전 단계를 완료했으며 labdb 데이터베이스, customers 테이블 및 orders 테이블에 데이터가 채워져 있는지 확인하십시오. 성능 비교를 더 의미 있게 만들기 위해 orders 테이블에 더 많은 데이터를 추가할 것입니다.

단계

  1. orders 테이블에 더 많은 데이터 추가:

    성능 비교를 더 현실적으로 만들기 위해 orders 테이블에 상당한 양의 데이터를 추가해 봅시다. 저장 프로시저를 사용하여 각 고객에 대해 1000 개의 주문을 삽입할 것입니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000 DO
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;

    설명:

    • 이 SQL 스크립트는 insert_many_orders라는 저장 프로시저를 생성합니다.
    • 프로시저는 네 명의 고객 각각에 대해 1000 개의 주문을 orders 테이블에 삽입합니다.
    • 데이터를 삽입한 후 프로시저는 삭제됩니다.

  2. IN을 사용한 쿼리:

    최소한 하나의 주문을 한 모든 고객을 찾기 위해 IN을 사용하는 다음 쿼리를 실행합니다.

    SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

  3. EXPLAIN을 사용하여 쿼리 실행 계획 분석:

    쿼리를 실행하기 전에 EXPLAIN 명령을 사용하여 쿼리 실행 계획을 분석합니다. 이를 통해 MySQL 이 쿼리를 실행하는 방법을 파악하고 잠재적인 성능 병목 현상을 식별할 수 있습니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

    EXPLAIN의 출력은 액세스되는 테이블, 사용되는 인덱스 (있는 경우) 및 작업이 수행되는 순서를 보여줍니다. 사용된 조인 또는 액세스 방법의 유형을 나타내는 type 열에 주의하십시오.

  4. EXISTS를 사용한 쿼리:

    최소한 하나의 주문을 한 모든 고객을 찾기 위해 EXISTS를 사용하는 다음 쿼리를 실행합니다.

    SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

  5. EXPLAIN을 사용하여 쿼리 실행 계획 분석:

    EXISTS 쿼리에 대한 쿼리 실행 계획을 분석하기 위해 EXPLAIN 명령을 사용합니다. MySQL 프롬프트에서 다음 SQL 명령을 실행합니다.

    EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

    IN 쿼리의 실행 계획과 비교하십시오. 액세스되는 테이블, 사용되는 인덱스 또는 액세스 방법에 차이가 있습니까?

  6. 관찰 결과:

    일반적으로 하위 쿼리가 많은 수의 행을 반환하는 경우 EXISTS가 IN보다 성능이 더 좋습니다. 이는 IN은 외부 쿼리의 값을 하위 쿼리가 반환한 모든 값과 비교해야 하는 반면, EXISTS는 일치하는 항목을 찾으면 즉시 중지하기 때문입니다. 그러나 실제 성능은 특정 쿼리, 데이터 및 데이터베이스 시스템에 따라 달라질 수 있습니다. 실행 시간의 더 정확한 측정을 위해 BENCHMARK() 함수 (원본 문서에 표시된 대로) 를 사용할 수 있지만, 이 실습에서는 EXPLAIN 출력 분석이 쿼리 계획에 대한 충분한 통찰력을 제공합니다.

  7. 정리 (선택 사항):

    데이터베이스 및 테이블을 정리하려면 MySQL 프롬프트에서 다음 명령을 실행할 수 있습니다.

    DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;

    모든 단계를 완료하면 다음을 입력하여 MySQL 클라이언트를 종료할 수 있습니다.

    exit

요약

이 실습에서는 SQL 문의 WHERE 절 내에서 하위 쿼리를 사용하여 다른 테이블 또는 동일한 테이블에서 파생된 조건에 따라 데이터를 필터링하는 방법을 배웠습니다. MySQL 서버에 연결하고, 데이터베이스 및 테이블을 생성하고, 샘플 데이터를 삽입하는 연습을 했습니다.

orders 테이블의 관련 데이터를 기반으로 고객을 찾기 위해 하위 쿼리와 함께 IN 연산자를 사용하는 방법을 탐색했습니다. 또한 IN의 대안으로 EXISTS 연산자에 대해 배우고 관련 행의 존재 여부를 확인하기 위해 사용하는 연습을 했습니다.

또한 외부 쿼리의 열을 참조하는 상관 하위 쿼리를 소개받았고, 이를 사용하여 평균 주문 금액을 초과하는 주문을 한 고객을 찾았습니다. 마지막으로 EXPLAIN 명령을 사용하여 실행 계획을 분석함으로써 IN 및 EXISTS 하위 쿼리의 성능을 비교하고 MySQL 이 이러한 쿼리를 처리하는 방법에 대한 통찰력을 얻었습니다.

관련 MySQL 코스
초보자를 위한 MySQL

초보자를 위한 MySQL

databasemysql
고급 MySQL 실습 랩

고급 MySQL 실습 랩

databasemysql
MySQL 사용자 권한 관리

MySQL 사용자 권한 관리

databasemysql