C# .NET - SQL UNION BUT SHOW ONLY DIFFERENT ROWS

Asked By Ismael on 21-Mar-12 01:07 PM
I'm joining two tables with UNION, but I only want to show the different rows, I mean only the rows that are in one table and are not in the other, I cant use a JOIN because i have no field where to join those two
Super Man replied to Ismael on 21-Mar-12 01:18 PM
you can check this alternative. : Didn't get another good idea but it works 4 u.

(select * from table1
UNION
SELECT * FROM TABLE2
)
MINUS
(
select * from table1
INTERSECT
SELECT * FROM TABLE2
)
Amit Choudhary replied to Ismael on 21-Mar-12 02:36 PM
We can do this way..
Select Distinct col1, Col2, col3
from
      (Select col1, Col2, col3  from Table1
      Union
       Select col1, Col2, col3  from Table2 ) X
Amit Choudhary replied to Amit Choudhary on 21-Mar-12 02:42 PM
In fact, if you simply use Union then it will give you all distinct row from both tables
Select col1, Col2, col3  from Table1
    Union
     Select col1, Col2, col3  from Table2

To get all the rows from both the tables use "Union All"
 
[)ia6l0 iii replied to Ismael on 21-Mar-12 09:22 PM
Union is not designed for this purpose. Union and all other commands assume that the SELECT queries return same type of results including the column count.  

In SQL 2005 and above, the EXCEPT keyword lets you queries rows from one table that aren't in another. For e.g. the following sql returns rows from table1 which are not in table2.
SELECT * FROM table1
EXCEPT
SELECT * FROM table2


Try this and let us know the results. We can take it forward then.

Hope this helps.

Reena Jain replied to Ismael on 22-Mar-12 03:44 AM
Hi,

The UNION,  EXCEPT and INTERSECT operators of SQL enable you to combine more than one SELECT statement  to form a single result set.
The UNION operator returns all rows.
The INTERSECT operator returns all rows that are in both result sets.
The EXCEPT operator returns the rows that are only in the first result set but not in the second

Here are example for both

'Except Example
 
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table1
GO
  
'Intersect Example
 
SELECT * FROM Table1
INTERSECT
SELECT * FROM Table1
GO


Pat Hartman replied to Ismael on 22-Mar-12 04:35 PM
To show rows that are in one table but not the other, you need to UNION two EXCEPT queries.
SELECT * FROM table1
 EXCEPT
 SELECT * FROM table2
UNION SELECT * FROM table2
 EXCEPT
 SELECT * FROM table1
The first select gives you the rows from table1 that are not in table2, the second select gives you rows from table2 that are not in table1.