CODEWITHSUNDEEP
sql-server
Tony Hendrix
Tony Hendrix

Reputation: 39

SQL Server table combination in a single query

In SQL Server, is it possible to, in a single query regardless of how complex, combine two table in the manner below?

Using a 'full join' I'll have to choose the .name from both tables resulting in duplicate columns, and using a 'union all' I'll get duplicates rows.

Table 1:

+---------+--------+
| name    | value1 |
+---------+--------+
| Abel    | a      |
| Baker   | b      |
+---------+--------+

Table 2:

+---------+--------+
| name    | value2 |
+---------+--------+
| Baker   | x      |
| Charlie | y      |
+---------+--------+

Query output:

+---------+--------+--------+
|  name   | value1 | value2 |
+---------+--------+--------+
| Abel    | a      | NULL   |
| Baker   | b      | x      |
| Charlie | NULL   | y      |
+---------+--------+--------+

Upvotes: 0

Views: 50

Answers (1)

Arulkumar
Arulkumar

Reputation: 13247

Using FULL OUTER JOIN you can achieve your expectation.

CREATE TABLE #table1 (name varchar (200), value1 VARCHAR(200))
INSERT INTO #table1
SELECT 'Abel', 'a' UNION 
SELECT 'Baker', 'b'
GO

CREATE TABLE #table2 (name varchar (200), value2 VARCHAR(200))
INSERT INTO #table2
SELECT 'Baker', 'x' UNION 
SELECT 'Charlie', 'y'
GO

SELECT ISNULL(t1.name, t2.name) AS name, t1.value1, t2.value2
FROM #table1 t1
FULL OUTER JOIN #table2 t2 ON t2.name = t1.name

DROP TABLE #table1
DROP TABLE #table2

Upvotes: 1

Related Questions