CODEWITHSUNDEEP
sql-server
SKLTFZ
SKLTFZ

Reputation: 950

SQL Server query about joining 2 table with union

I have two tables T1 and T2, both of them contain a column ID and value.

I need to join T1 and T2 into T3 by means of ID.

T3 should contain columns ID, T1Value, and T2Value.

The main requirement is, ID of T1 and T2 is possible not mapped

T1:

ID   VALUE
-----------
1    hi
2    hello

T2:

ID   VALUE
----------
2    kitty
3    dog

Then, T3 should be

ID   T1VALUE   T2VALUE
----------------------
 1   hi
 2   hello     kitty
 3             dog

Is it possible to achieve this without using pivot table, or temp table (ideally should be a single executable query)?

Thanks.

Upvotes: 0

Views: 48

Answers (1)

Squirrel
Squirrel

Reputation: 24763

You can use FULL OUTER JOIN

SELECT ID      = COALESCE(T1.ID, T2.ID),
       T1VALUE = T1.VALUE,
       T2VALUE = T2.VALUE
FROM   T1
       FULL OUTER JOIN T2
       ON  T1.ID = T2.ID

There are lots of example on FULL OUTER JOIN . just search for it

Another way is to use UNION ALL

SELECT T1.ID, T1VALUE = T1.VALUE, T2VALUE = NULL
FROM   T1

UNION ALL

SELECT T2.ID, T1VALUE = NULL,     T2VALUE = T2.VALUE
FROM   T2

Upvotes: 1

Related Questions