SQL에서 중첩된 쿼리 사용하는 방법

튜토리얼
MySQL

소개

구조화된 질의 언어(SQL)는 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 관리하는 데 사용됩니다. SQL에서 유용한 기능 중 하나는 쿼리 내에서 쿼리를 만드는 것인데, 이를 하위 쿼리 또는 중첩된 쿼리라고도 합니다. 중첩된 쿼리는 일반적으로 괄호로 묶인 SELECT 문이며, 기본 SELECT, INSERT, 또는 DELETE 작업 내에 삽입됩니다.

이 자습서에서는 중첩된 쿼리를 SELECT, INSERT, 그리고 DELETE 문과 함께 사용합니다. 또한 중첩된 쿼리 내에서 집계 함수를 사용하여 WHERE 및 LIKE 절에 지정된 정렬된 데이터 값과 데이터 값을 비교합니다.

전제 조건

이 안내서를 따르려면 SQL을 사용하는 관계형 데이터베이스 관리 시스템(RDBMS)을 실행하는 컴퓨터가 필요합니다. 이 자습서의 지침과 예제는 다음 환경에서 확인되었습니다:

  • A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
  • 서버에 MySQL이 설치되고 보안이 설정되었습니다. 이를 설정하려면 우분투 20.04에 MySQL 설치 방법 가이드를 따르십시오. 이 가이드는 이 가이드의 단계 3에서 설명된 대로 비루트 MySQL 사용자를 설정한 것으로 가정합니다.

참고: 관계형 데이터베이스 관리 시스템은 고유한 SQL 구현을 사용합니다. 이 튜토리얼에서 설명된 명령은 대부분의 RDBMS에서 작동하지만 MySQL 이외의 시스템에서 테스트하면 정확한 구문 또는 출력이 다를 수 있습니다.

이 튜토리얼에서 중첩된 쿼리를 사용하는 연습을 하려면 샘플 데이터로 로드된 데이터베이스와 테이블이 필요합니다. 삽입할 준비가된 것이 없는 경우 다음 MySQL에 연결하고 샘플 데이터베이스 설정 섹션을 읽어 데이터베이스 및 테이블을 만드는 방법을 알아보십시오. 이 튜토리얼에서는 이 샘플 데이터베이스와 테이블을 계속 참조합니다.

MySQL에 연결하고 샘플 데이터베이스 설정

SQL 데이터베이스가 원격 서버에서 실행되는 경우 로컬 머신에서 서버에 SSH로 연결하십시오:

  1. ssh sammy@your_server_ip

다음으로, MySQL 프롬프트를 열어 sammy를 사용자 계정 정보로 대체하십시오:

  1. mysql -u sammy -p

zooDB라는 데이터베이스를 만듭니다:

  1. CREATE DATABASE zooDB;

만약 데이터베이스가 성공적으로 생성되었다면 다음 출력을 받게 될 것입니다:

Output
Query OK, 1 row affected (0.01 sec)

zooDB 데이터베이스를 선택하려면 다음 USE 문을 실행하세요:

  1. USE zooDB;



Output
Database changed

데이터베이스를 선택한 후에는 그 안에 테이블을 만들어야 합니다. 이 튜토리얼의 예제에서는 동물원을 방문한 손님에 대한 정보를 저장하는 테이블을 만들겠습니다. 이 테이블은 다음 일곱 개의 열을 보유합니다:

  • guest_id: 동물원을 방문한 손님의 값을 저장하며 int 데이터 유형을 사용합니다. 이는 또한 테이블의 기본 키로 작동하여 이 열의 각 값이 해당 행의 고유 식별자로 기능합니다.
  • first_name: 각 손님의 이름을 저장하는데, varchar 데이터 유형을 사용하며 최대 30자까지 허용됩니다.
  • last_name: 다시 말해, 각 손님의 성을 저장하는데 varchar 데이터 유형을 사용하며 최대 30자까지 허용됩니다.
  • guest_type: 각 손님의 유형(성인 또는 어린이)을 포함하며 varchar 데이터 유형을 사용하며 최대 15자까지 허용됩니다.
  • membership_type: 각 손님의 멤버십 유형을 나타내며, 최대 30자까지 허용하는 varchar 데이터 유형을 사용합니다.
  • membership_cost: 다양한 멤버십 유형의 비용을 저장합니다. 이 열은 decimal 데이터 유형을 사용하며 정밀도는 다섯 자리이고 소수점 오른쪽에 두 자리까지 허용됩니다.
  • total_visits: 각 손님의 총 방문 횟수를 기록하는 데 int 데이터 유형을 사용합니다.

다음 CREATE TABLE 명령을 실행하여 각 열을 포함하는 guests라는 테이블을 만듭니다:

  1. CREATE TABLE guests (

  2. guest_id int, 

  3. first_name varchar(30),

  4. last_name varchar(30),

  5. guest_type varchar(15),

  6. membership_type varchar(30),

  7. membership_cost decimal(5,2), 

  8. total_visits int,

  9. PRIMARY KEY (guest_id)

  10. );

그런 다음 빈 테이블에 샘플 데이터를 삽입합니다:

  1. INSERT INTO guests

  2. (guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)

  3. VALUES

  4. (1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),

  5. (2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),

  6. (3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),

  7. (4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),

  8. (5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),

  9. (6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),

  10. (7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),

  11. (8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),

  12. (9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),

  13. (10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),

  14. (11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 

  15. (12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 

  16. (13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 

  17. (14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),

  18. (15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);



Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

데이터를 삽입한 후에는 SQL에서 중첩 쿼리를 사용할 준비가 되었습니다.

SELECT와 함께 중첩된 쿼리 사용하기

SQL에서 쿼리는 데이터베이스의 테이블에서 데이터를 검색하는 작업이며 항상 SELECT 문을 포함합니다. 중첩된 쿼리는 다른 작업 내에 포함된 완전한 쿼리입니다. 중첩된 쿼리에는 일반적인 쿼리에서 사용되는 모든 요소가 있을 수 있으며 어떤 유효한 쿼리든 다른 작업 내에 포함되어 중첩된 쿼리가 될 수 있습니다. 예를 들어 중첩된 쿼리는 INSERT 및 DELETE 작업 내에 포함될 수 있습니다. 작업에 따라 중첩된 쿼리는 특정 작업 순서를 따르기 위해 문을 올바른 괄호 안에 포함해야 합니다. 중첩된 쿼리는 또한 여러 명령을 하나의 쿼리 문으로 실행하고자 할 때 유용하며, 여러 개를 작성하여 원하는 결과를 반환하는 대신에 사용됩니다.

중첩된 쿼리를 더 잘 이해하기 위해 이전 단계의 샘플 데이터를 사용하여 어떻게 유용한지 설명해 보겠습니다. 예를 들어, 동물원을 더 자주 방문한 guests 테이블의 모든 손님을 찾고 싶다고 가정해 보겠습니다. 다음과 같은 쿼리로 이 정보를 찾을 수 있다고 가정할 수 있습니다:

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > AVG(total_visits);

그러나 이 구문을 사용한 쿼리는 오류를 반환합니다:

Output
ERROR 1111 (HY000): Invalid use of group function

이 오류의 원인은 평균 함수인 AVG()가 SELECT 절 내에서 실행되지 않으면 작동하지 않기 때문입니다.

이 정보를 검색하는 한 가지 방법은 먼저 손님 방문의 평균 수를 찾는 쿼리를 실행한 다음 그 값을 기반으로 결과를 찾는 다른 쿼리를 실행하는 것입니다. 다음 두 예제에서와 같습니다:

  1. SELECT AVG(total_visits) FROM guests;



Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > 57.5333;



Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

그러나 첫 번째 쿼리(SELECT AVG(total_visits) FROM guests;)를 두 번째 쿼리 내에서 중첩함으로써 동일한 결과 집합을 단일 쿼리로 얻을 수 있습니다. 중첩된 쿼리를 사용할 때 적절한 양의 괄호를 사용하여 수행하려는 작업을 완료하는 것이 필요합니다. 이것은 중첩된 쿼리가 먼저 실행되는 첫 번째 작업이기 때문입니다:

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > 

  4. (SELECT AVG(total_visits) FROM guests);



Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

이 출력에 따르면 다섯 명의 손님이 평균 이상으로 방문했습니다. 이 정보는 현재 회원들이 동물원을 자주 방문하고 매년 멤버십 패스를 갱신하는 창의적인 방법을 생각하는 데 유용한 통찰을 제공할 수 있습니다. 또한, 이 예제는 두 개의 별도 쿼리를 실행하는 대신 원하는 결과를 얻기 위해 하나의 완전한 문으로 중첩된 쿼리를 사용하는 가치를 보여줍니다.

INSERT와 함께 중첩된 쿼리 사용

중첩된 쿼리를 사용하면 다른 SELECT 문 안에 넣는 것으로만 제한되지 않습니다. 사실, 중첩된 쿼리를 사용하여 중첩된 쿼리를 INSERT 작업 안에 넣어 기존 테이블에 데이터를 삽입할 수도 있습니다.

일러스트로 설명하자면, 연계 동물원에서 손님들에 대한 일부 정보를 요청하여 그들의 위치에서 “거주자” 멤버십을 구입한 손님들에게 15% 할인을 제공하고자 합니다. 이를 위해 CREATE TABLE을 사용하여 upgrade_guests라는 새 테이블을 생성합니다. 여섯 개의 열을 보유하도록 합니다. 데이터 유형(int 및 varchar과 같은)과 최대 문자 수와 같은 데이터 유형에 유의하십시오. 이러한 데이터 유형이 샘플 데이터베이스 섹션에서 생성한 guests 테이블의 원래 데이터 유형과 일치하지 않으면 guests 테이블에서 데이터를 삽입하려고 할 때 오류가 발생하고 데이터가 올바르게 전송되지 않습니다. 다음 정보를 사용하여 테이블을 만듭니다:

  1. CREATE TABLE upgrade_guests (

  2. guest_id int, 

  3. first_name varchar(30),

  4. last_name varchar(30),

  5. membership_type varchar(30),

  6. membership_cost decimal(5,2),

  7. total_visits int, 

  8. PRIMARY KEY (guest_id)

  9. );

일관성과 정확성을 위해이 테이블에서 대부분의 데이터 유형 정보를 guests 테이블과 동일하게 유지했습니다. 또한 새 테이블에 필요하지 않은 추가 열을 제거했습니다. 이 비어 있는 테이블이 준비되면 다음 단계는 테이블에 원하는 데이터 값을 삽입하는 것입니다.

이 작업에서는 데이터가 삽입되는 위치에 대한 명확한 방향이 있도록 INSERT INTO 및 새로운 upgrade_guests 테이블을 작성합니다. 다음으로, 관련 데이터 값을 검색하기 위한 SELECT 문과 이러한 값이 guests 테이블에서 오는지 확인하기 위한 FROM을 작성하는 중첩된 쿼리를 작성합니다.

또한 “Resident” 회원 중에서 어떠한 것에도 15% 할인을 적용하려면 중첩된 쿼리 문장(membership_cost * 0.85) 내에서 곱셈 수학 연산인 *을 포함하여야 합니다. 그런 다음 WHERE 절을 사용하여 membership_type 열의 값을 정렬하십시오. LIKE 절을 사용하여 “Resident” 멤버십에 대한 결과만을 좁힐 수 있으며, 이 경우에는 동일한 용어를 따르는 멤버십을 선택하기 위해 단일 인용부호 안에 “Resident”라는 단어 앞뒤에 백분율 % 기호를 넣으십시오. 다음과 같이 쿼리를 작성하십시오:

  1. INSERT INTO upgrade_guests

  2. SELECT guest_id, first_name, last_name, membership_type, 

  3. (membership_cost * 0.85), total_visits

  4. FROM guests

  5. WHERE membership_type LIKE '%resident%';



Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

출력 결과에서는 새로운 upgrade_guests 테이블에 다섯 개의 레코드가 추가되었음을 나타냅니다. 요청한 데이터가 성공적으로 guests 테이블에서 생성한 빈 upgrade_guests 테이블로 전송되었는지 확인하고 중첩된 쿼리와 WHERE 절에 지정한 조건으로 다음을 실행하십시오:

  1. SELECT * FROM upgrade_guests;



Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

새로운 upgrade_guests 테이블에서의 이 출력에 따르면, “Resident” 관련 손님 회원 정보가 올바르게 guest 테이블에서 삽입되었습니다. 또한 15% 할인이 적용된 새로운 membership_cost가 다시 계산되었습니다. 결과적으로 이 작업은 적절한 대상을 분류하고 목표로 하는 데 도움이 되었으며, 할인된 가격이 이러한 잠재적인 새 회원들과 공유할 준비가 되어 있습니다.

중첩 쿼리를 사용하여 DELETE

중첩 쿼리를 사용하여 DELETE 문을 연습하려면, 현재 동물원을 자주 방문하는 손님들을 제거하고 업그레이드 프리미엄 패스 할인을 현재 자주 방문하지 않는 회원들에게 집중하려고 합니다.

이 작업을 시작하려면 DELETE FROM 문을 사용하여 데이터가 삭제되는 위치를 명확하게하십시오. 이 경우 upgrade_guests 테이블입니다. 그런 다음 WHERE 절을 사용하여 중첩 쿼리에서 지정된 양보다 더 많은 total_visits를 정렬하십시오. 중첩된 쿼리를 사용하여 SELECT를 사용하여 total_visits의 평균, AVG,을 찾으십시오. 따라서 앞의 WHERE 절에는 비교할 적절한 데이터 값이 있습니다. 마지막으로, FROM을 사용하여 해당 정보를 guests 테이블에서 검색하십시오. 전체 쿼리 문은 다음과 같습니다:

  1. DELETE FROM upgrade_guests

  2. WHERE total_visits >

  3. (SELECT AVG(total_visits) FROM guests);



Output
Query OK, 2 rows affected (0.00 sec)

upgrade_guests 테이블에서 해당 레코드가 성공적으로 삭제되었는지 확인하고 total_visits를 숫자 및 오름차순으로 결과를 정렬하십시오.

참고: 새 테이블에서 레코드를 삭제하기 위해 DELETE 문을 사용하면 원본 테이블에서는 삭제되지 않습니다. SELECT * FROM original_table을 실행하여 새 테이블에서 삭제되었더라도 모든 원본 레코드가 계산되는지 확인할 수 있습니다.

  1. SELECT * FROM upgrade_guests ORDER BY total_visits;



Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

이 출력에서 확인할 수 있듯이, DELETE 문과 중첩된 쿼리는 지정된 데이터 값 삭제 작업을 올바르게 수행했습니다. 이 테이블은 이제 평균 방문 횟수보다 적은 세 명의 손님 정보를 보유하고 있으며, 이는 동물원 대표가 그들에게 프리미엄 패스를 할인된 가격에 업그레이드하도록 제안하고, 동물원을 더 자주 방문하도록 독려하기 위한 훌륭한 시작점입니다.

결론

중첩된 쿼리는 별도의 쿼리를 실행할 때에만 얻을 수 있는 매우 상세한 결과를 얻을 수 있기 때문에 유용합니다. 또한 중첩된 쿼리를 사용하여 INSERT 및 DELETE 문을 사용하면 데이터를 한 단계에서 삽입하거나 삭제할 수 있는 다른 방법을 제공합니다. 데이터를 구성하는 방법에 대해 더 알고 싶다면, SQL 사용 방법 시리즈를 확인해보세요.

Source:
https://www.digitalocean.com/community/tutorials/how-to-use-nested-queries