통합 쿼리를 사용하여 여러 쿼리를 단일 결과로 결합

Northwind 데이터베이스를 사용하는 이전 섹션의 예제에서는 두 테이블의 데이터만 결합됩니다. 그러나 통합 쿼리에서는 세 개 이상의 테이블을 쉽게 결합할 수 있습니다. 예를 들어 이전 예제를 기반으로 작성할 경우 쿼리 출력에 ‘직원’ 이름도 포함할 수 있습니다. 세 번째 쿼리를 추가하고 다음과 같은 추가 UNION 키워드를 통해 이전 SQL 문과 결합하여 해당 작업을 수행할 수 있습니다.

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

데이터시트 보기에서 결과를 볼 경우 모든 직원이 예제 회사 이름과 함께 나열되므로 매우 유용하지 않을 수 있습니다. 해당 필드로 개인이 사내 직원, 공급자 또는 고객인지 표시하려면 회사 이름 대신 ‘고정 값’을 포함할 수 있습니다. SQL은 다음과 같이 표시됩니다.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

데이터시트 보기의 결과는 다음과 같이 표시됩니다. Access가 다음과 같은 5개의 레코드를 표시합니다.

위의 쿼리는 Access가 통합 쿼리의 첫 번째 쿼리에서 출력 필드의 이름만 읽기 때문에 추가로 축소할 수 있습니다. 다음은 두 번째 및 세 번째 쿼리 섹션에서 출력을 제거한 것입니다.

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Access 통합 쿼리에서는 순서 지정이 한 번만 허용되지만 각 쿼리는 개별적으로 필터링될 수 있습니다. 이전 섹션의 통합 쿼리를 기반으로 빌드할 경우 WHERE 절을 추가하여 각 쿼리를 필터링한 위치의 예는 다음과 같습니다.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

데이터시트 보기로 전환하면 다음과 유사한 결과가 표시됩니다.

통합할 쿼리가 서로 다른 경우에는 출력 필드가 서로 다른 데이터 형식의 데이터를 결합해야 하는 상황이 발생할 수 있습니다. 이 경우 통합 쿼리는 텍스트 데이터 형식이 텍스트 ‘및’ 숫자를 모두 포함할 수 있으므로 결과를 텍스트 데이터 형식으로 반환합니다.

이 작업의 작동 방식을 이해하기 위해 Northwind 예제 데이터베이스의 ‘제품 트랜잭션’ 통합 쿼리를 사용합니다. 해당 예제 데이터베이스를 열고 나서 데이터시트 보기에서 제품 트랜잭션 쿼리를 엽니다. 마지막 10개의 레코드는 다음 출력과 유사합니다.

수량 필드를 구매 및 판매로 분할하려 한다고 가정해 보겠습니다. 값이 없는 필드에 대해 고정 0 값을 포함하려 한다고 가정해 보겠습니다. SQL이 이 통합 쿼리를 찾는 방법은 다음과 같습니다.

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

데이터시트 보기로 전환하면 다음과 같이 마지막 10개의 레코드가 표시됩니다.

이 예제를 계속 진행해서 0이 포함된 필드를 비워 두려는 경우 어떻게 하나요? 다음과 같이 Null 키워드를 추가하여 0 대신 아무것도 표시하지 않도록 SQL을 수정할 수 있습니다.

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

그러나 데이터시트 보기로 전환되었으므로 예기치 않은 결과가 발생합니다. 구매 열에서 모든 필드가 지워집니다.

이 상황이 발생하는 이유는 Access에서는 필드의 데이터 형식을 첫 번째 쿼리에서 확인하기 때문입니다. 이 예제에서 Null은 숫자가 아닙니다.

그러면 필드의 빈 값에 대해 빈 문자열을 삽입하고 삽입하려고 하면 어떻게 되나요? 이 시도에 대한 SQL은 다음과 같이 표시됩니다.

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

데이터시트 보기로 전환하면 Access가 구매 값을 검색하지만 값을 텍스트로 값을 변환한 것을 알 수 있습니다. 값이 데이터시트 보기에서 왼쪽 맞춤으로 정렬되므로 텍스트 값이라는 것을 알 수 있습니다. 첫 번째 쿼리의 빈 문자열은 이러한 결과가 표시되는 이유를 나타내는 숫자가 아닙니다. 또한 구매 레코드에 빈 문자열이 포함되어 있으므로 판매 값도 텍스트로 변환됩니다.

이 퍼즐을 어떻게 해결하나요?

솔루션은 필드 값을 숫자로 예상하는 쿼리를 적용하는 것입니다. 다음 식을 사용하여 이 작업을 수행할 수 있습니다.

IIf(False, 0, Null)

확인할 조건 False는 True가 될 수 없으므로 식은 항상 Null을 반환하지만, Access는 두 가지 출력 옵션을 모두 계속 평가하고 출력을 숫자 또는 Null로 결정합니다.

작업 예제에서 이 식을 사용하는 방법은 다음과 같습니다.

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

두 번째 쿼리는 수정할 필요가 없습니다.

데이터시트 보기로 전환하면 원하는 결과가 표시됩니다.

동일한 결과를 얻는 대체 방법은 통합 쿼리에서 쿼리 앞에 다른 쿼리를 추가하는 것입니다.

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

각 필드에 대해 Access는 사용자가 정의하는 데이터 형식의 고정 값을 반환합니다. 물론 이 쿼리의 출력이 결과를 방해하지 않도록 WHERE 절을 False로 포함하는 요령을 사용할 수 있습니다.

WHERE False

이 절은 항상 false여서 쿼리가 아무것도 반환하지 않으므로 이 방법은 간단한 요령입니다. 이 문을 기존 SQL과 결합하면 다음과 같이 완료된 문이 생성됩니다.

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

통합 쿼리의 특별한 경우는 하나 이상의 필드 합계를 포함하는 레코드와 레코드 집합을 결합하는 것입니다.

통합 쿼리에서 합계를 구하는 방법을 설명하기 위해 Northwind 예제 데이터베이스에서 만들 수 있는 또 다른 예제는 다음과 같습니다.

1. 다음 SQL 구문을 사용하여 맥주(제품 ID=Northwind 데이터베이스의 34) 구매를 표시하는 간단한 새 쿼리를 만듭니다.

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. 데이터시트 보기로 전환하면 다음 4개의 구매가 표시됩니다.

   받은 날짜	수량
   2006/1/22	100
   2006/1/22	60
   2006/4/4	50
   2006/4/5	300

3. 합계를 구하려면 다음 SQL을 사용하여 단순 집계 쿼리를 만듭니다.

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. 데이터시트 보기로 전환하면 다음 하나의 레코드만 표시됩니다.

   MaxOfDate Received	SumOfQuantity
   2006/4/5	510

5. 이러한 두 쿼리를 통합 쿼리로 결합하여 전체 수량이 포함된 레코드를 구매 레코드에 추가합니다.

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

6. 데이터시트 보기로 전환하면 각각의 합계, 이어서 수량을 합계하는 레코드가 포함된 4개의 구매가 표시됩니다.

   받은 날짜	수량
   2006/1/22	60
   2006/1/22	100
   2006/4/4	50
   2006/4/5	300
   2006/4/5	510

통합 쿼리에 합계를 추가하는 방법에 대한 기본 사항을 설명합니다. 합계 레코드를 다른 레코드와 시각적으로 구별하려면 “세부 정보” 및 “합계”와 같은 두 쿼리에 모두 고정 값을 포함할 수도 있습니다. 통합 쿼리에서 세 개 이상의 테이블 또는 쿼리 결합 섹션에서 고정 값 사용에 대해 검토할 수 있습니다.

기본적으로 Access의 통합 쿼리에는 고유 레코드만 포함됩니다. 그러나 모든 레코드를 포함하려면 어떻게 하나요? 여기서는 다른 예제가 유용할 수 있습니다.

이전 섹션에서는 통합 쿼리에서 합계를 만드는 방법을 설명했습니다. 제품 ID=48을 포함하도록 통합 쿼리 SQL을 수정합니다.

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

데이터시트 보기로 전환하면 약간 잘못된 결과가 표시됩니다.

물론 하나의 레코드는 전체적으로 수량을 두 번 반환하지 않습니다.

이 결과가 표시되는 이유는 Purchase Order Details 테이블에 기록된 대로 동일한 수량의 초콜릿이 두 번 판매되었기 때문입니다. Northwind 예제 데이터베이스의 두 레코드를 모두 표시하는 간단한 선택 쿼리 결과는 다음과 같습니다.

이전에 언급한 통합 쿼리에서 구매 주문 ID 필드가 포함되지 않았고 두 개의 필드가 두 개의 고유 레코드를 구성하지 않음을 알 수 있습니다.

모든 레코드를 포함하려면 SQL에서 UNION 대신 UNION ALL을 사용합니다. 이렇게 하면 결과 정렬에 영향을 줄 가능성이 높으므로 ORDER BY 절을 포함하여 정렬 순서를 결정할 수도 있습니다. 이전 예제를 작성하는 수정된 SQL은 다음과 같습니다.

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

데이터시트 보기로 전환하면 마지막 레코드로 합계 외에 모든 세부 정보가 표시됩니다.

통합 쿼리는 일반적으로 폼에서 콤보 상자 컨트롤의 레코드 원본으로 사용됩니다. 이 콤보 상자를 사용하여 폼의 레코드를 필터링할 값을 선택할 수 있습니다. 예를 들어 도시별로 직원 레코드를 필터링합니다.

이 작업의 작동 방식을 확인하기 위해 Northwind 예제 데이터베이스에서 다음과 같은 다른 예제를 만들어 이 시나리오를 설명할 수 있습니다.

1. 이 SQL 구문을 사용하여 단순 선택 쿼리를 만듭니다.

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. 데이터시트 보기로 전환하면 다음 결과가 표시됩니다.

   구/군/시	필터
   과천시	과천시
   수원	수원
   삼신	삼신
   성대	성대
   과천시	과천시
   삼신	삼신
   과천시	과천시
   삼신	삼신
   과천시	과천시

3. 결과를 확인하면 유용한 정보라고 생각되지 않을 수 있습니다. 다음 SQL을 사용하여 쿼리를 확장하고 통합 쿼리로 변환합니다.

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. 데이터시트 보기로 전환하면 다음 결과가 표시됩니다.

   구/군/시	필터
   <모두>	*
   수원	수원
   성대	성대
   삼신	삼신
   과천시	과천시

   Access는 <모두> 및 “*”의 고정 필드 값을 사용하여 이전에 표시된 9개의 레코드를 통합합니다.

   이 통합 절에는 UNION ALL이 포함되지 않으므로 Access는 각 도시가 동일한 고정 값으로 한 번만 반환되는 고유 레코드만 반환합니다.

5. 이제 각 도시 이름을 한 번만 표시하는 완료된 통합 쿼리를 완료했으므로, 모든 도시를 효과적으로 선택하는 옵션과 함께 이 쿼리를 폼에서 콤보 상자의 레코드 원본으로 사용할 수 있습니다. 이 특정 예제를 모델로 사용하여 폼에서 콤보 상자 컨트롤을 만들고, 이 쿼리를 레코드 원본으로 설정하고, 필터 열의 열 너비 속성을 0(영)으로 설정하여 열을 시각적으로 숨긴 다음, 바인딩된 열 속성을 1로 설정하여 두 번째 열의 색인을 표시합니다. 그런 다음 폼 자체의 필터 속성에서, 콤보 상자 컨트롤에서 선택된 값을 사용하여 폼 필터를 활성화하는 다음과 같은 코드를 추가할 수 있습니다.

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

   폼의 사용자는 폼 레코드를 특정 도시 이름으로 필터링하거나 <모두>를 선택하여 모든 도시의 모든 레코드를 나열할 수 있습니다.

맨 위로 이동