CODEWITHSUNDEEP
sqlsql-server-2008
MadHenchbot
MadHenchbot

Reputation: 1385

Union of two tables with the same columns and same where clause

I have two SQL tables in two different locations that contain the exact same fields, just different data (all unique SKUs). I'd like to join the tables and run a complex WHERE clause on the result for logging purposes.

What I have in place works, but it appears massively redundant (I'm coming from a C# background here). 

select sku, catalogname, getDate()
from uswebdb.commerce.catalogproducts
where sku is not null
and (CategoryName is not null or ParentOID = 113)
and (sku not like '%[a-z]%')
union all
select sku, catalogname, getDate()
from ukwebdb.commerce.catalogproducts
where sku is not null
and (CategoryName is not null or ParentOID = 113)
and (sku not like '%[a-z]%')

Is there a more concise way to join these two tables and produce a similar result, or is this the best approach? The selected fields will always be the same, but the number of tables involved and the complexity of the where clause might increase in the near future.

I guess ideally I'd like to see something like this:

select sku, catalogname, getDate() from
uswebdb.commerce.catalogproducts
--magic join/union--
ukwebdb.commerce.catalogproducts
-- more joins...--
where sku is not null
and (CategoryName is not null or ParentOID = 113)
and (sku not like '%[a-z]%')

Is that even possible in SQL2008? Am I just really over-thinking this?

Upvotes: 2

Views: 3091

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

Are these on different servers or different databases, because it makes a big difference. Your syntax implies that are on the same server, different database, which means you can move the WHERE outside:

select sku, catalogname, getdate()
from
(
select sku, catalogname, categoryname, parentOID
from uswebdb.commerce.catalogproducts
union all
select sku, catalogname, categoryname, parentOID
from ukwebdb.commerce.catalogproducts
) F
where (F.CategoryName is not null or F.ParentOID = 113)
and (F.sku not like '%[a-z]%')

You shold use CTRL-L to see if the query plan is different. There might be performance repercussions.

Upvotes: 2

Alexander Bell
Alexander Bell

Reputation: 7918

You have asked a reasonable question, but first, let's clarify the terminology. UNION and JOIN are rather different type of operations: the first one (namely, UNION) that you are using merges 2 tables "vertically" while JOIN expands "horizontally". You SQL statement seems to be correct, even though it looks a bit redundant (from your perspective). I would recommend to filter out rows from both tables before merging (exactly as it's implemented). The only modification worth to try is to use UNION instead of UNION ALL (no duplicates).

Upvotes: 1

Related Questions