question

Bala avatar image
Bala asked

Join identical tables

I am using Sql 2k5. I have 2 tables thats are identical in all aspects except the data. I need to join the 2 tables using the same WHERE condition. How do i do it without using a UNION?

For example, the 2 tables are goodCustomer and badCustomer.

I would like to join the data in these 2 tables using a WHERE condition like

WHERE login_Date > '2009-01-01' or DealNumber = 0

How would the query change if the data is mutually exclusive and if it isnt.

Any help is appreciated.

Thanks

sql-server-2005select
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

Why don't you wan't to use UNION? I am not sure what you want to accomplish, but have you tried INTERSECT or EXCEPT? I give you some samples for you to try.

This select will return every record from table1 where there are matching records in table2:

SELECT someCol1, someCol2 FROM table1 
WHERE login_Date > '2009-01-01' or DealNumber = 0
INTERSECT 
SELECT someCol1, someCol2 FROM table2 
WHERE login_Date > '2009-01-01' or DealNumber = 0

This select will return every record from table1 where there are no matching records in table2:

SELECT someCol1, someCol2 FROM table1 
WHERE login_Date > '2009-01-01' or DealNumber = 0
EXCEPT
SELECT someCol1, someCol2 FROM table2 
WHERE login_Date > '2009-01-01' or DealNumber = 0

Another solution may be FULL OUTER JOIN, the following sample will give you every record from table1 and table2 that matches your where clause. If there records in table1 that does not have any records in table2, you will get NULL for the table2 columns, etc. :

SELECT * FROM table1 AS t1 
FULL OUTER JOIN table2 as t2 
ON t1.yourPrimaryKey = t2.yourPrimaryKey
WHERE 
(t1.login_Date > '2009-01-01' 
OR t1.DealNumber = 0 
OR t1.yourPrimaryKey IS NULL)
AND 
(t2.login_Date > '2009-01-01' 
OR t2.DealNumber = 0 
OR t2.yourPrimaryKey IS NULL)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

In addition to the other suggestions, since you're trying to combine the data from the two tables, you should probably be using UNION ALL. With that you won't see the aggregation functions that are normally associated with a UNION, so the performance should be very acceptable.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

Can I ask why you do not want to use a UNION? It seems that UNION, or as Grant suggested UNION ALL is exactly what you want if I understand the rest of the question correctly.

If the problem is that you do not want two separate sets of where conditions then you can use a subquery or CTE. For instance you could use something like:

With CustomerCTE as
(
select * 
from goodCustomer 
UNION ALL
select * 
from badCustomer)

select *
from CustomerCTE
WHERE login_Date > '2009-01-01' or DealNumber = 0
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.