UNION vs. VIEW

Question

UNION vs. VIEW

psstarkey

SSC Veteran

Points: 291
More actions
August 5, 2002 at 8:21 am

#158626

I need to combine two tables of homogenous data and was considering a UNION of the two. But I am wondering if it would be more efficient overall to do a VIEW which would effectively UNION the two together.
I believe the query with the UNION would get used quite often so there would be some caching going on, but I wondered if it would just be more efficient to have a VIEW keeping the UNION intact all the time.
Thanks for any and all suggestions!
-p-

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

If you are going to use the UNION frequently, using a view is a good way to go. You can even assign permissions against the view, giving you some options from a security perspective.

From a cost perspective, they should be about the same. However, one thing the view will give you is the ability to apply a WHERE clause against the UNIONed queries all at once.

Here's an example from the Northwind database:

The basic union:



SELECT Address, City, Region, PostalCode, Country

FROM Customers

UNION 

SELECT Address, City, Region, PostalCode, Country

FROM Employees

The view:



CREATE vw_Locations

AS

SELECT Address, City, Region, PostalCode, Country

FROM Customers

UNION 

SELECT Address, City, Region, PostalCode, Country

FROM Employees

In order to duplicate:



SELECT * 

FROM vw_Locations

WHERE Region IS NULL

you would have to apply the WHERE clause against both queries:



SELECT Address, City, Region, PostalCode, Country

FROM Customers

WHERE Region IS NULL

UNION 

SELECT Address, City, Region, PostalCode, Country

FROM Employees

WHERE Region IS NULL

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

mdburr Default port Points: 1483 More actions · Answer 2

Why would he have to apply the where clause against both queries? Couldn't he just do:



SELECT *

  FROM (SELECT Address, City, Region, PostalCode, Country

          FROM Customers

         UNION

        SELECT Address, City, Region, PostalCode, Country

          FROM Employees) AS SubQuery

 WHERE SubQuery.Region IS NULL

Matthew Burr