Join identical tables

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)

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.

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