웹사이트 검색

SQL에서 공용체를 사용하는 방법

저자는 Write for DOnations 프로그램을 선택했습니다.

소개

많은 데이터베이스는 의미와 컨텍스트에 따라 서로 다른 테이블에 정보를 분산시킵니다. 종종 데이터베이스 내에 있는 데이터에 대한 정보를 검색할 때 한 번에 둘 이상의 테이블을 참조하려고 합니다.

관계형 데이터베이스 시스템. UNION 작업은 일치하는 열이 있는 두 쿼리의 결과를 가져와서 하나로 병합합니다.

이 가이드에서는 UNION 작업을 사용하여 둘 이상의 테이블에서 동시에 데이터를 검색한 다음 결과를 결합합니다. 또한 UNION 연산자를 필터링과 결합하여 결과를 정렬합니다.

전제 조건

이 가이드를 따르려면 SQL 기반 관계형 데이터베이스 관리 시스템(RDBMS)을 실행하는 컴퓨터가 필요합니다. 이 가이드의 지침과 예제는 다음 환경을 사용하여 검증되었습니다.

  • Ubuntu 20.04용 초기 서버 설정 가이드에 설명된 대로 관리 권한이 있는 루트가 아닌 사용자와 UFW로 구성된 방화벽이 있는 Ubuntu 20.04를 실행하는 서버
  • 3단계에 설명된 대로 서버에 MySQL을 설치하고 보호합니다.
  • How To SELECT Rows FROM Tables in SQL 가이드에 설명된 대로 SELECT 쿼리를 실행하여 데이터베이스에서 데이터를 선택하는 것과 관련된 기본 지식.

참고: 많은 RDBMS는 고유한 SQL 구현을 사용합니다. 이 자습서에 설명된 명령은 대부분의 RDBMS에서 작동하고 표준 SQL 구문의 일부이지만 MySQL 이외의 시스템에서 테스트하는 경우 정확한 구문이나 출력이 다를 수 있습니다.

또한 UNION 연산을 사용하여 연습할 수 있도록 샘플 데이터가 로드된 일부 테이블이 있는 데이터베이스가 필요합니다. MySQL 서버에 연결하고 이 가이드의 예제에 사용된 샘플 데이터베이스를 생성하는 방법에 대한 자세한 내용은 MySQL에 연결 및 샘플 데이터베이스 설정 섹션을 참조하십시오.

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

이 섹션에서는 이 가이드의 예제를 따를 수 있도록 MySQL 서버에 연결하고 샘플 데이터베이스를 생성합니다.

SQL 데이터베이스 시스템이 원격 서버에서 실행되는 경우 로컬 시스템에서 서버로 SSH합니다.

  1. ssh sammy@your_server_ip

그런 다음 MySQL 서버 프롬프트를 열고 sammy를 MySQL 사용자 계정의 이름으로 바꿉니다.

  1. mysql -u sammy -p

bookstore라는 데이터베이스를 만듭니다.

  1. CREATE DATABASE bookstore;

데이터베이스가 성공적으로 생성되면 다음과 같은 출력을 받게 됩니다.

Output
Query OK, 1 row affected (0.01 sec)

bookstore 데이터베이스를 선택하려면 다음 USE 문을 실행합니다.

  1. USE bookstore;

다음 출력을 받게 됩니다.

Output
Database changed

데이터베이스를 선택한 후 그 안에 샘플 테이블을 생성할 수 있습니다. 이 가이드의 목적을 위해 책 구매와 대여를 모두 제공하는 가상의 서점을 사용합니다. 두 서비스는 별도로 관리됩니다. 따라서 구매 및 임대에 대한 데이터는 별도의 테이블에 저장됩니다.

참고: 이 예제의 데이터베이스 스키마는 교육 목적으로 단순화되었습니다. 실제 시나리오에서는 테이블 구조가 더 복잡하고 관계형 데이터베이스 이해가 필요합니다.

첫 번째 테이블인 book_purchases에는 구매한 책과 구매한 고객에 대한 데이터가 포함됩니다. 네 개의 열이 있습니다.

  • purchase_id: 이 열에는 int 데이터 유형으로 표시되는 구매 식별자가 있습니다. 이 열은 테이블의 기본 키가 되며 각 값은 해당 행의 고유 식별자가 됩니다.
  • customer_name: 이 열에는 최대 30자의 varchar 데이터 유형을 사용하여 표현되는 고객의 이름이 포함됩니다.
  • book_title: 이 열에는 최대 200자의 varchar 데이터 유형을 사용하여 표현된 구입한 책의 제목이 있습니다.
  • 날짜: 날짜 데이터 유형을 사용하면 이 열에 각 구매 날짜가 표시됩니다.

다음 명령을 사용하여 샘플 테이블을 생성합니다.

  1. CREATE TABLE book_purchases (

  2.     purchase_id int,

  3.     customer_name varchar(30),

  4.     book_title varchar(40),

  5.     date date,

  6.     PRIMARY KEY (purchase_id)

  7. );

다음 출력이 인쇄되면 첫 번째 테이블이 생성된 것입니다.

Output
Query OK, 0 rows affected (0.00 sec)

두 번째 테이블은 book_leases라고 하며 빌린 책에 대한 정보를 저장합니다. 구조는 이전과 유사하지만 임대는 임대 날짜와 임대 기간이라는 두 가지 날짜로 특징지어집니다. 이를 나타내기 위해 임대 테이블에는 5개의 열이 있습니다.

  • lease_id: 이 열에는 int 데이터 유형으로 표시되는 임대 식별자가 있습니다. 이 열은 테이블의 기본 키가 되며 각 값은 해당 행의 고유 식별자가 됩니다.
  • customer_name: 이 열에는 최대 30자의 varchar 데이터 유형을 사용하여 표현되는 고객의 이름이 포함됩니다.
  • book_title: 이 열에는 최대 200자의 varchar 데이터 유형을 사용하여 표현되는 빌린 책의 제목이 있습니다.
  • date_from: date 데이터 유형을 사용하면 이 열에 임대 시작 날짜가 표시됩니다.
  • date_to: date 데이터 유형을 사용하면 이 열에 임대 종료 날짜가 표시됩니다.

다음 명령을 사용하여 두 번째 테이블을 만듭니다.

  1. CREATE TABLE book_leases (

  2.     lease_id int,

  3.     customer_name varchar(30),

  4.     book_title varchar(40),

  5.     date_from date,

  6.     date_to date,

  7.     PRIMARY KEY (lease_id)

  8. );

다음 출력은 두 번째 테이블 생성을 확인합니다.

Output
Query OK, 0 rows affected (0.00 sec)

그런 다음 다음 INSERT INTO 작업을 실행하여 일부 샘플 데이터가 포함된 구매 테이블을 로드합니다.

  1. INSERT INTO book_purchases

  2. VALUES

  3. (1, 'sammy', 'The Picture of Dorian Gray', '2022-10-01'),

  4. (2, 'sammy', 'Pride and Prejudice', '2022-10-04'),

  5. (3, 'sammy', 'The Time Machine', '2022-09-23'),

  6. (4, 'bill', 'Frankenstein', '2022-07-23'),

  7. (5, 'bill', 'The Adventures of Huckleberry Finn', '2022-10-01'),

  8. (6, 'walt', 'The Picture of Dorian Gray', '2022-04-15'),

  9. (7, 'walt', 'Frankenstein', '2022-10-13'),

  10. (8, 'walt', 'Pride and Prejudice', '2022-10-19');

INSERT INTO 작업은 지정된 값을 가진 8개의 구매를 book_purchases 테이블에 추가합니다. 다음 출력은 8개의 행이 모두 추가되었음을 나타냅니다.

Output
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

그런 다음 일부 샘플 데이터를 book_leases 테이블에 삽입합니다.

  1. INSERT INTO book_leases

  2. VALUES

  3. (1, 'sammy', 'Frankenstein', '2022-09-14', '2022-11-14'),

  4. (2, 'sammy', 'Pride and Prejudice', '2022-10-01', '2022-12-31'),

  5. (3, 'sammy', 'The Adventures of Huckleberry Finn', '2022-10-01', '2022-12-01'),

  6. (4, 'bill', 'The Picture of Dorian Gray', '2022-09-03', '2022-09-18'),

  7. (5, 'bill', 'Crime and Punishment', '2022-09-27', '2022-12-05'),

  8. (6, 'kim', 'The Picture of Dorian Gray', '2022-10-01', '2022-11-15'),

  9. (7, 'kim', 'Pride and Prejudice', '2022-09-08', '2022-11-17'),

  10. (8, 'kim', 'The Time Machine', '2022-09-04', '2022-10-23');

샘플 데이터가 추가되었음을 확인하는 다음 출력을 받게 됩니다.

Output
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

임대 및 구매는 유사한 고객 및 책 제목과 관련되며 UNION 연산자 동작을 시연하는 데 유용합니다.

이것으로 가이드의 나머지 부분을 따르고 SQL에서 UNION 작업을 사용할 준비가 되었습니다.

UNION 연산자 구문 이해

SQL의 UNION 연산자는 개별 SELECT 쿼리를 통해 검색된 두 개의 개별 결과 집합을 두 쿼리에서 반환된 행을 포함하는 하나의 결과 집합으로 병합하도록 데이터베이스에 지시합니다.

참고: 데이터베이스는 UNION과 함께 사용되는 SELECT 쿼리의 복잡성을 제한하지 않습니다. 데이터 검색 쿼리에는 하위 쿼리가 포함될 수 있습니다. 종종 UNION은 복잡한 문의 결과를 병합하는 데 사용됩니다. 교육 목적으로 이 가이드의 예제에서는 SELECT 쿼리를 사용하여 UNION 연산자의 작동 방식에 초점을 맞춥니다.

다음 예는 UNION 연산자를 포함하는 SQL 문의 일반 구문을 보여줍니다.

  1. SELECT column1, column2 FROM table1

  2. UNION

  3. SELECT column1, column2 FROM table2;

이 SQL 조각은 table1에서 두 개의 열을 반환하는 SELECT 문으로 시작하고 그 뒤에 UNION 연산자와 두 번째 SELECT 문. 두 번째 SELECT 쿼리도 두 개의 열을 반환하지만 table2에서 가져온 것입니다. UNION 키워드는 데이터베이스에 이전 및 다음 쿼리를 가져와 별도로 실행한 다음 결과 집합을 하나로 결합하도록 지시합니다. SELECT 쿼리와 그 사이의 UNION 키워드를 모두 포함하는 전체 코드 조각은 단일 SQL 문입니다. 그 때문에 첫 번째 SELECT 쿼리는 세미콜론으로 끝나지 않으며 전체 문 뒤에만 나타납니다.

예를 들어 책을 구입했거나 대여한 모든 고객을 나열한다고 가정합니다. 구매 기록은 book_purchases 테이블에 보관되는 반면 임대는 book_leases 테이블에 저장됩니다. 다음 쿼리를 실행합니다.

  1. SELECT customer_name FROM book_purchases

  2. UNION

  3. SELECT customer_name FROM book_leases;

다음은 이 쿼리의 결과 집합입니다.

Output
+---------------+
| customer_name |
+---------------+
| sammy         |
| bill          |
| walt          |
| kim           |
+---------------+
4 rows in set (0.000 sec)

이 출력은 Sammy, Bill, Walt 및 Kim이 특정 시점에 책을 구입했거나 임대했음을 나타냅니다. 이 결과 집합이 어떻게 생성되었는지 이해하려면 두 개의 SELECT 문을 별도로 실행해 보십시오. 한 번은 구매용이고 다른 한 번은 임대용입니다.

다음 쿼리를 실행하여 책을 구매한 고객을 반환합니다.

  1. SELECT customer_name FROM book_purchases;

다음 출력이 화면에 인쇄됩니다.

Output
+---------------+
| customer_name |
+---------------+
| sammy         |
| sammy         |
| sammy         |
| bill          |
| bill          |
| walt          |
| walt          |
| walt          |
+---------------+
8 rows in set (0.000 sec)

Sammy, Bill, Walt는 책을 구입했지만 Kim은 구입하지 않았습니다.

다음으로 쿼리를 실행하여 책을 임대한 고객을 반환합니다.

  1. SELECT customer_name FROM book_leases;

다음 출력이 화면에 인쇄됩니다.

Output
+---------------+
| customer_name |
+---------------+
| sammy         |
| sammy         |
| sammy         |
| bill          |
| bill          |
| kim           |
| kim           |
| kim           |
+---------------+
8 rows in set (0.000 sec)

임대 테이블은 Sammy, Bill 및 Kim을 참조하지만 Walt는 결코 책을 빌리지 않습니다. 두 답변을 결합하여 임대 및 구매에 대한 데이터를 얻습니다.

UNION을 사용하는 것과 두 개의 쿼리를 개별적으로 실행하는 것의 중요한 차이점은 UNION이 결과를 병합하는 것 외에도 중복 값을 제거한다는 것입니다. 고객 이름이 결과에서 반복되지 않습니다.

UNION을 사용하여 두 개의 개별 쿼리 결과를 올바르게 병합하려면 두 쿼리 모두 동일한 형식으로 결과를 반환해야 합니다. 일부 불일치는 데이터베이스 엔진 오류를 일으키고 다른 불일치는 쿼리 의도와 일치하지 않는 결과를 제공합니다.

다음 두 가지 예를 고려하십시오.

열 개수가 일치하지 않는 UNION

단일 열을 반환하는 SELECT 문과 두 개의 열을 반환하는 다른 문 사이에서 UNION을 실행해 보세요.

  1. SELECT purchase_id, customer_name FROM book_purchases

  2. UNION

  3. SELECT customer_name FROM book_leases;

데이터베이스 서버는 오류로 응답합니다.

Output
The used SELECT statements have a different number of columns

열 개수가 다른 결과 집합에 대해 UNION 작업을 수행하는 것은 불가능합니다.

열 순서가 일치하지 않는 UNION

동일한 값을 반환하지만 다른 순서로 반환하는 두 SELECT 문 사이에서 UNION을 실행해 보십시오.

  1. SELECT customer_name, book_title FROM book_purchases

  2. UNION

  3. SELECT book_title, customer_name FROM book_leases;

데이터베이스 서버는 오류를 반환하지 않지만 결과 집합은 올바르지 않습니다.

Output
+------------------------------------+------------------------------------+
| customer_name                      | book_title                         |
+------------------------------------+------------------------------------+
| sammy                              | The Picture of Dorian Gray         |
| sammy                              | Pride and Prejudice                |
| sammy                              | The Time Machine                   |
| bill                               | Frankenstein                       |
| bill                               | The Adventures of Huckleberry Finn |
| walt                               | The Picture of Dorian Gray         |
| walt                               | Frankenstein                       |
| walt                               | Pride and Prejudice                |
| Frankenstein                       | sammy                              |
| Pride and Prejudice                | sammy                              |
| The Adventures of Huckleberry Finn | sammy                              |
| The Picture of Dorian Gray         | bill                               |
| Crime and Punishment               | bill                               |
| The Picture of Dorian Gray         | kim                                |
| Pride and Prejudice                | kim                                |
| The Time Machine                   | kim                                |
+------------------------------------+------------------------------------+
16 rows in set (0.000 sec)

이 예에서 UNION 작업은 첫 번째 쿼리의 첫 번째 열을 두 번째 쿼리의 첫 번째 열과 병합하고 두 번째 열에 대해 동일한 작업을 수행하여 고객 이름과 책 제목을 혼합합니다.

WHERE 절 사용 및 UNION과 함께 주문

이전 예에서는 두 개의 해당 테이블에 있는 모든 행을 나타내는 결과 집합을 병합했습니다. 종종 결과를 병합하기 전에 행을 필터링해야 합니다. UNION 연산자와 병합된 SELECT 문은 WHERE 절을 사용하여 병합할 수 있습니다.

구매 또는 임대를 통해 서점의 도움을 받아 Sammy가 어떤 책을 읽는지 알고 싶다고 가정합니다. 다음 쿼리를 실행합니다.

  1. SELECT book_title FROM book_purchases

  2. WHERE customer_name = 'Sammy'

  3. UNION

  4. SELECT book_title FROM book_leases

  5. WHERE customer_name = 'Sammy';

두 SELECT 쿼리에는 WHERE 절이 포함되어 있으며, Sammy의 구매 및 임대만 포함하도록 두 개의 개별 테이블에서 행을 필터링합니다. 이 쿼리에 대한 결과 집합은 다음과 같이 인쇄됩니다.

Output
+------------------------------------+
| book_title                         |
+------------------------------------+
| The Picture of Dorian Gray         |
| Pride and Prejudice                |
| The Time Machine                   |
| Frankenstein                       |
| The Adventures of Huckleberry Finn |
+------------------------------------+
5 rows in set (0.000 sec)

다시 한 번 UNION은 결과 목록에 중복이 없도록 합니다. WHERE 절을 사용하여 두 SELECT 쿼리 또는 둘 중 하나만 반환되는 행을 제한할 수 있습니다. 또한 WHERE 절은 두 문에서 서로 다른 열과 조건을 참조할 수 있습니다.

UNION 작업을 통해 반환된 결과는 특정 순서를 따르지 않습니다. 이를 변경하려면 ORDER BY 절을 활용할 수 있습니다. 정렬은 개별 쿼리가 아닌 최종 병합 결과에 대해 수행됩니다.

Sammy가 구입하거나 임대한 모든 책 목록을 검색한 후 책 제목을 알파벳순으로 정렬하려면 다음 쿼리를 실행합니다.

  1. SELECT book_title FROM book_purchases

  2. WHERE customer_name = 'Sammy'

  3. UNION

  4. SELECT book_title FROM book_leases

  5. WHERE customer_name = 'Sammy'

  6. ORDER BY book_title;

다음 출력이 화면에 인쇄됩니다.

Output
+------------------------------------+
| book_title                         |
+------------------------------------+
| Frankenstein                       |
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
| The Time Machine                   |
+------------------------------------+
5 rows in set (0.001 sec)

이번에는 두 SELECT 쿼리에서 병합된 결과를 포함하는 book_title 열을 기반으로 한 순서대로 결과가 반환됩니다.

UNION ALL을 사용하여 중복 유지

이전 예제에서 설명한 것처럼 UNION 연산자는 결과에서 중복 행을 자동으로 제거합니다. 그러나 때로는 이 동작이 쿼리를 통해 기대하거나 의도한 것과 다를 수 있습니다. 예를 들어 2022년 10월 1일에 구입했거나 임대한 책에 관심이 있다고 가정합니다. 이러한 제목을 검색하려면 이전과 유사한 예를 따를 수 있습니다.

  1. SELECT book_title FROM book_purchases

  2. WHERE date = '2022-10-01'

  3. UNION

  4. SELECT book_title FROM book_leases

  5. WHERE date_from = '2022-10-01'

  6. ORDER BY book_title;

다음과 같은 결과를 얻게 됩니다.

Output
+------------------------------------+
| book_title                         |
+------------------------------------+
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
+------------------------------------+
3 rows in set (0.001 sec)

반환된 책 제목은 정확하지만 이 책이 구매만 되었는지, 대여만 되었는지 또는 둘 다인지 결과에서 알 수 없습니다. 일부 책을 구입하고 임대한 경우 제목이 book_purchases 및 book_leases 테이블 모두에 나타납니다. 그러나 UNION이 중복 행을 제거한 결과 결과에서 해당 정보가 손실됩니다.

다행스럽게도 SQL에는 이 동작을 변경하고 중복 행을 유지할 수 있는 방법이 있습니다. UNION ALL 연산자를 사용하여 중복 행을 제거하지 않고 두 쿼리의 결과를 병합할 수 있습니다. UNION ALL은 UNION과 유사하게 작동하지만 동일한 값이 여러 번 발생하는 경우 모두 결과에 나타납니다.

동일한 쿼리를 실행하되 UNION을 UNION ALL로 변경합니다.

  1. SELECT book_title FROM book_purchases

  2. WHERE date = '2022-10-01'

  3. UNION ALL

  4. SELECT book_title FROM book_leases

  5. WHERE date_from = '2022-10-01'

  6. ORDER BY book_title;

이번에는 결과 목록이 더 길어집니다.

Output
+------------------------------------+
| book_title                         |
+------------------------------------+
| Pride and Prejudice                |
| The Adventures of Huckleberry Finn |
| The Adventures of Huckleberry Finn |
| The Picture of Dorian Gray         |
| The Picture of Dorian Gray         |
+------------------------------------+
5 rows in set (0.000 sec)

허클베리 핀의 모험과 도리안 그레이의 그림 두 권의 책이 결과 집합에 두 번 나타납니다. 이는 이러한 책이 book_purchases 및 book_leases 테이블 모두에 나타남을 의미합니다. 중복 항목의 경우 해당 날짜에 임대 및 구매가 모두 이루어졌다고 가정할 수 있습니다.

중복 항목을 제거할지 또는 유지할지 여부에 따라 UNION 및 UNION ALL 연산자 중에서 선택할 수 있으며 이 연산자는 상호 교환하여 사용할 수 있습니다.

참고: UNION ALL을 실행하는 것이 UNION을 실행하는 것보다 더 빠릅니다. 데이터베이스가 중복에 대해 결과 세트를 스캔할 필요가 없기 때문입니다. 중복 행을 포함하지 않을 것으로 알고 있는 두 SELECT 쿼리의 결과를 병합하는 경우 UNION ALL을 사용하면 더 큰 데이터 세트에서 눈에 띄는 성능 향상을 가져올 수 있습니다.

결론

이 가이드에 따라 UNION 및 UNION ALL 작업을 사용하여 여러 테이블에서 데이터를 검색했습니다. 또한 WHERE 절을 사용하여 결과를 필터링하고 ORDER BY 절을 사용하여 순서를 지정했습니다. 마지막으로 SELECT 문이 다른 데이터 형식을 생성하는 경우 발생할 수 있는 오류 및 예기치 않은 동작에 대해 배웠습니다.

여기에 포함된 명령은 대부분의 관계형 데이터베이스에서 작동하지만 모든 SQL 데이터베이스는 고유한 언어 구현을 사용합니다. (이러한 차이점에 대한 자세한 내용은 SQLite vs MySQL vs PostgreSQL: 관계형 데이터베이스 관리 시스템 비교 가이드를 참조하세요.) 각 명령과 전체 옵션 세트에 대한 자세한 설명은 RDBMS의 공식 문서를 참조해야 합니다.

SQL 언어와 작업에 대한 다양한 개념에 대해 자세히 알아보려면 SQL 사용 방법 시리즈의 다른 가이드를 확인하는 것이 좋습니다.