Get data from both tables what join should I use

Posted on

Question :

I have T1 as table 1

----------------
field1 | field2
---------------
 val1  | val2

I have T2 as table 2

----------------
field1 | field2
---------------
 val11  | val22

I want to write a query which can produce result like this

 field1 | field2 | T1.T2 | 
 ------------------------|
 val11  | val22  | T2    | 
 val1   | val2   | T1    | 

Answer :

Simply, you can use a CTE or SubQuery then ORDER BY [T1.T2] DESC as

WITH CTE AS
(
  SELECT Col1, Col2, 'T1' [T1.T2]
  FROM T1
  UNION
  SELECT Col1, Col2, 'T2'
  FROM T2
)
SELECT *
FROM CTE
ORDER BY [T1.T2] DESC;

Live Demo

Maybe this query is useful… I used union to join the 2 tables.

mysql> select field1 ,field2 from T2 union select field1, field2 from T1;

Leave a Reply

Your email address will not be published. Required fields are marked *