SQL Union 에 대해서

데이터를 종적으로 결합하는데에는 UNION 이외에는 방법이 없다.
UNION은 데이터를 종적으로 결합하며, 결합되는 각 컬럼간의 형식이 일치하면 하나의 집합으로 도출할수 있다. ALL 옵션이 없으면 두결과 집합을 대상으로 중복되는 로우(Duplicated Rows)를 모두 제거하며, ALL이 없으면 이 작업을 수행하지 않는다.

select *
from pubs..employee
where pub_id = '0877'
union
select *
from pubs..employee
where pub_id = '9952'

UNION의 장점

UNION할수 잇는 단 한가지는 서로 다른 두집합을 하나의 집합으로 합치는 것이다. 연결해서 붙이면 둘이상의 집합들을 붙이는데 도움이 된다.

동일한 테이블만이 아닌 다른테이블에서도 강제로 데이터형식을 맞춘다면 하나의 결과 집합으로 만들수 있다. 이는 테이블수준을 넘어 다른 데이터베이스라도 가능하다.(Open 쿼리를 사용할경우)
이런 쿼리들은 특정한 조건에서만 효율적으로 동작한다. 예를 들어 전체 범위로 쿼리를 실행하는 것보다, 범위를 몇가지로 나누어서 개별적으로 수행하는 쿼리의 성능이 더 나을수 있다.

이런 경우의 쿼리들을 UNION ALL로 합쳐서 쿼리를 수행하도록 조정하는 경우가 UNION을 사용해서 더 좋아지는 경우다.
성능의 관점에서 인덱스가 포함된 임시테이블을 생성해서 데이터를 처리하는것보다 UNION을 사용하여 결과집합을 합쳐서 처리하는것이 더 좋은 경우가 많다.

select 'pubs' as dbname, emp_id, (fname + ''+ minit+ '' +lname) as contactname
from pubs..employee
union all
select 'northwind', CustomerID, ContactName
from Northwind..Customers

이 쿼리는 서로 다른 두 테이블을 하나의 RowSet으로 묶어내는 UNION 쿼리다. 하나는 pubs..employee이고, 다른하나는 Northwind..Customers 테이블이다.

서로 다른 스키마 구조를 가지고 있지만, 컬럼 형식과 순서를 맞추면 하나로 통합할수 있다. 게다가 각각의 결과 집합의 원본을 나타내도록 가상 컬럼을 삽입하면, 행 단위로도 구분이 가능하다.

select 'pubs' as dbname, emp_id, (fname + ''+ minit+ '' +lname) as contactname
from pubs..employee
union all
select 'northwind', CustomerID, ContactName
from Northwind..Customers
union all
select 'implicit', 'EM0001', 'Something M Special'

UNION을 사용하면 테이블로 존재하지 않는 임의의 행집합을 결과집합에 추가할수도 있다. 마지막 select문은 테이블이 아닌 단순 Select 문으로 구성된 “Constant Scan”이다.

UNION은 가상의 임의 집합을 구성할수있게 하므로서 물리적으로 어떤 데이터를 저장하지 않는다 하더라도 이 값들을 사용할수 있게끔 한다.
만일 하나의 쿼리를 통해, 여분의 스키마 추가 없이, 여분의 INSERT 문 없이, 모든 작업을 완성할수 있도록 하려면 이 UNION이라는 것을 사용해야 한다.

간단한 넘버 더블링(Number Doubling) 문제를 고려해보자.

넘버 더블링을 프로시저로 구현하기 위해서는 물리적인 구조를 가지는 임의의 테이블을 생성하고 여기에 INSERT 문으로 값을 넣어줘야 할것이다.

create table numDb1 (num int)

declare @num int
set @num = 1
while(@num <= 100)
begin
insert numDb1 values(@num)
set @num = @num + 1
end

이에 반해 UNION 쿼리 방식의 넘버 더블링은 다음과 같을것이다. 쿼리가 길어 V_NUM 이라는 뷰를 생성하였으며, 이 뷰는 물리적인 구조를 가지지 않으며(Constant Scan) 단순히 UNION 방식의 쿼리문만을 저장하고 있을뿐이다.

create view v_num as
select 0 num
union all
select 1 num
union all
select 2 num
union all
select 3 num
union all
select 4 num
union all
select 5 num
union all
select 6 num
union all
select 7 num
union all
select 8 num
union all
select 9 num

넘버 더블링 하기 위해서 쿼리를 셀프(self)로 크로스 조인(Cross Join) 하면 그뿐이다.

select (n10.num * 10 + n1.num * 1) + 1 as num
from v_num as n10, v_num as n1

UNION 쿼리를 사용하면 쿼리의 정렬옵션을 좀더 디테일 하게 조정할수 있다. 보통 Case When 구문을 사용해 정렬을 조정하지만 UNION도 이러한 일을 처리할수 있다. 특히 범위에 관련되어 정렬해야 하는경우라면, 범위에 인덱스가 잡혀있다면 UNION은 꾀나편한 정렬처리기법이 된다.게다가 이건 다시한번 Sort 하는 작업도 필요없다. 이 기법의 장점은 “Concatenation”작업이 순차적으로 결과집합을 붙이기 때문이다.

select fname
from pubs..employee
where fname like 'P%'
union all
select fname
from pubs..employee
where fname like 'A%'

위의 쿼리에서 fname 이 P 로 시작하는것을 우선해서 정렬하고 싶다는경우 각각을 결과 집합으로 뽑아서 이를 UNION ALL 하게 되면, 결과집합이 처리되는데로 붙어서 최종 결과집합이 처리된다.
단 UNION ALL이 아닌 UNION 은 안된다. UNION 은 Sort/Distinct 작업을 처리하기 때문에 전체적인 정렬작업을 거치게 된다.

UNION ALL쿼리로 정렬할경우 주의해야 할 사항

UNION 쿼리안의 결과집합에서 ORDER BY 가 들어가야 하는경우, 이런경우 몇가지 트릭이 필요하다. 우선 ORDER BY 에 지정된 컬럼이 SELECT 목록에도 지정되어야만 한다. 그리고 직접적으로 UNION ALL과 ORDER BY 키워드를 같이 사용할수 없다. 이런경우 ORDER BY가 포함된 SELECT문을 인라인 서브쿼리로 처리해야만 할것이다.

select lname
from
(
select top 100 percent lname
from pubs..employee
where pub_id = '0877'
order by lname
) as x
union all
select '*************'
union all
select lname
from
(
select top 100 percent lname
from pubs..employee
where emp_id = '0877'
order by lname desc
) as y

위 쿼리로 볼때 희망하는 결과는 “1234 ****** 4321” 이런식으로 결과가 나와야 하는데 실제로는 영 딴판으로 결과가 나오게 된다.

쿼리 계획에서 제일하단의 실행계획은 쿼리가 DESC로 정렬순서를 주고 있으므로 BACKWARD 연산이 되어있어야만 역순으로 정렬된다. 하지만 마우스를 올리면 전혀 BACKWARD 되어있지 않아서 1234******1234 가 되버리는것이다.
이 문제를 해결하려면 결과 집합에 제한을 주도록 쿼리문을 구성해야한다. 99 퍼센트로 수치를 줄일수도 있고, 100개라던지 양사로 줄일수도 있다. 퍼센트로 줄이면 반드시 결과값이 전부다 나오지는 않으므로 개수로 주자. (쿼리를 실행할때 예상하는 결과 row 수가 150 일때 top 500 이런식으로)

* 이부분을 실행해 보았지만 결과는 같았다. 시스템정보 : sql server 2008

상기의 내용은 손호성님의 “SQL Server 2005 완벽가이드 책”을 보며 필기한 내용입니다.