Best practices for querying tables residing in different database using JOIN statements

Question

Hello and thank you very much for your time. I am using SQL Server 2008. My challenge is as follows:

I have 3 databases Q_DB, CAN_DB, USA_DB. CAN_DB and USA_DB have the same tables but represent two different countries

The Q_DB has tables of queues. One table is called ACCOUNTS and the columns of interest are ACCOUNTID and SERVER

I want to query the information on the other two databases from the fields of ACCOUNTS. An example is:

Q_DB.dbo.ACCOUNTS

ID123 CAN_DB
ID456 USA_DB
ID789 CAN_DB

I know I can do it this way, but it I was wondering if there is any other approach to obtain the result.

SELECT ACTNAME, AMTDUE, SHIPCOUNTY  
FROM CAN_DB.dbo.Accts 
WHERE ACCTID IN(SELECT ACCOUNTID FROM Q_DB.dbo.ACCOUNTS WHERE SERVER IN('CAN_DB')
UNION ALL
SELECT ACTNAME, AMTDUE, SHIPCOUNTRY 
FROM USA_DB.dbo.Accts
WHERE ACCTID IN(SELECT ACCOUNTID FROM Q_DB.dbo.ACCOUNTS WHERE SERVER IN('USA_DB')

Any help or opinions or suggestions would be greatly appreciated . Thanks

So you are saying you want to UNION the results of two different queries, and you are wondering if there is a better way to do it than using a UNION? I'm not sure what you are expecting. — cadrell0
– cadrell0, Commented Oct 22, 2012 at 18:04
There is no SQL Server 2010 - there's versions 2000, 2005, 2008, 2008 R2 and 2012 - but no 2010 version.... so which one are you really using?? — marc_s
– marc_s, Commented Oct 22, 2012 at 18:05
I am trying to see if Using WHERE with the UNION is the smartest approach or if using a different method I can take away the UNION (since i am basically rewriting the code over) And thanks for your time — jnoel10
– jnoel10, Commented Oct 22, 2012 at 18:08
@jnoel10 The fact is, there are 2 different tables, in 2 different databases. You need to union them. They way you are doing it is fine. However, I would question that specific WHERE. Do you expect the USA_DB to have an ACCTID with a SERVER other than USA_DB? — cadrell0
– cadrell0, Commented Oct 22, 2012 at 18:15

Francis P · Accepted Answer · 2012-10-22 18:21:20Z

3

I don't see why you are using IN with a single value (CAN_DB).

Also, you should precognize EXISTS instead of IN.

SELECT ACTNAME, AMTDUE, SHIPCOUNTRY  
FROM CAN_DB.dbo.Accts a1
WHERE EXISTS (SELECT * FROM Q_DB.dbo.ACCOUNTS WHERE ACCTID = a1.ACCTID AND SERVER = 'CAN_DB')
UNION ALL
SELECT ACTNAME, AMTDUE, SHIPCOUNTRY 
FROM USA_DB.dbo.Accts a2
WHERE EXISTS (SELECT * FROM Q_DB.dbo.ACCOUNTS WHERE ACCTID = a2.ACCTID AND SERVER = 'USA_DB')

Note: Using UNION ALL will return duplicates (if there are some). To have the query select unique rows, use the keyword UNION instead.

EDIT: If you want to sum the AMTDUE from both servers according to same ACTNAME and SHIPCOUNTRY :

SELECT  COALESCE(can.ACTNAME,usa.ACTNAME) as ACTNAME,
        COALESCE(can.AMTDUE,0)+COALESCE(usa.AMTDUE,0) as AMTDUE,
        COALESCE(can.SHIPCOUNTRY,usa.SHIPCOUNTRY) as SHIPCOUNTRY  
FROM    CAN_DB.dbo.Accts can
FULL OUTER JOIN USA_DB.dbo.Accts usa
ON      can.ACTNAME = usa.ACTNAME AND 
        can.SHIPCOUNTRY = usa.SHIPCOUNTRY

edited Oct 22, 2012 at 18:21

answered Oct 22, 2012 at 18:03

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

jnoel10 Over a year ago

Thank you for your response, I agree that EXISTS looks much better than IN. I was trying to see if there is a way i could call this without using the UNION at all. The reason I use the UNION ALL is due to the fact that it is possible that I will have duplicate ACCTIDs but from the different servers

Francis P Over a year ago

Are the duplicate ACCTID related with each other? if so, it can be done using a JOIN... I guess you'd want to sum the AMTDUE from both servers according to same ACCTID and SHIPCOUNTRY ?

jnoel10 Over a year ago

Sadly they may not be related (it is a data integrity issue that I will also be resolving but is slow going) and that is why I did not use JOIN, but I am a novice to SQL and still learning :)

Francis P Over a year ago

Ok, I understand the ACCTID is not the same, but could the ACTNAME be the same?

Francis P Over a year ago

As for EXISTS instead of IN, it is much more performant to use EXISTS. IN would select every row of the table and then compare the returned IDs versus EXISTS will stop selecting as soon as 1 row, satisfying the condition, is returned.

|

Collectives™ on Stack Overflow

Best practices for querying tables residing in different database using JOIN statements

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related