using where clause with Union

Question

I have two tables, t1 and t2, with identical columns(id, desc) and data. But one of the columns, desc, might have different data for the same primary key, id.

I want to select all those rows from these two tables such that t1.desc != t2.desc

select a.id, b.desc 
FROM (SELECT * FROM t1 AS a
      UNION ALL 
      SELECT * FROM t2 AS b)
WHERE a.desc != b.desc

For example, if t1 has (1,'aaa') and (2,'bbb') and t2 has(1,'aaa') and (2,'bbb1') then the new table should have (2,'bbb') and (2,'bbb1')

However, this does not seem to work. Please let me know where I am going wrong and what is the right way to do it right.

Answer 1

Union is not going to compare the data.You need Join here

SELECT * 
FROM t1 AS a 
inner join  t2 AS b
on a.id =b.id 
and a.desc != b.desc

Answer 2

UNION ALL dumps all rows of the second part of the query after the rows produced by the first part of the query. You cannot compare a's fields to b's, because they belong to different rows.

What you are probably trying to do is locating records of t1 with ids matching these of t2, but different description. This can be achieved by a JOIN:

SELECT a.id, b.desc
FROM t1 AS a
JOIN t2 AS b ON a.id = b.id
WHERE a.desc != b.desc

This way records of t1 with IDs matching records of t2 would end up on the same row of joined data, allowing you to do the comparison of descriptions for inequality.

I want both the rows to be selected is the descriptions are not equal

You can use UNION ALL between two sets of rows obtained through join, with tables switching places, like this:

SELECT a.id, b.desc -- t1 is a, t2 is b
FROM t1 AS a
JOIN t2 AS b ON a.id = b.id
WHERE a.desc != b.desc
    UNION ALL
SELECT a.id, b.desc -- t1 is b, t2 is a
FROM t2 AS a
JOIN t1 AS b ON a.id = b.id
WHERE a.desc != b.desc