Joining tables on columns with different names but

问题:

I'm not sure how to concisely formulate question to describe a problem I would like to solve.
I have two following tables:
Table 1

[idA] [numA]
 NULL   8
 1      10
 2      15
 3      16

Table 2

[idB] [numB]
 2      14
 3      30
 4      32

Now, I'm not sure how to formulate T-Sql query to produce following result:

[id] [numA] [numB]
NULL  8      0
1     10     0
2     15     14
3     16     30
4     0      32

Are there any suggestions on how to solve this?

UPDATE:

---

Would there be any problems with @AdaTheDev's script if there was one more table (idC, numC) to join? In that case what would be the best solution? The thing is I have 15 of them to join into one table and they should be grouped by id and have 15 corresponding numX columns.

回答1:

Something like this, should do it

SELECT ISNULL(t1.idA, t2.idB) AS id, 
    ISNULL(t1.numA, 0) AS numA, 
    ISNULL(t2.NumB, 0) AS numB
FROM table1 t1
    FULL OUTER JOIN table2 t2 ON t1.idA = t2.idB OR t1.ida IS NULL AND t2.idb IS NULL

Update
Note I've added an OR condition to the join to handle the case where idA and idB are NULL, to give a single result

Full test script (with added NULL id record in table2):

DECLARE @Table1 TABLE (ida integer, numA INTEGER)
DECLARE @Table2 TABLE (idb integer, numb INTEGER)

INSERT @Table1 ([ida], [numA])
VALUES (NULL, 8), (1, 10), (2, 15), (3, 16)

INSERT @Table2 ([idb], [numb])
VALUES (NULL, 9), (2, 14), (3, 30), (4, 32)

SELECT ISNULL(t1.idA, t2.idB) AS id, 
    ISNULL(t1.numA, 0) AS numA, 
    ISNULL(t2.NumB, 0) AS numB
FROM @table1 t1
    FULL OUTER JOIN @table2 t2 ON t1.idA = t2.idB OR t1.ida IS NULL AND t2.idb IS NULL

回答2:

DECLARE @table1 AS TABLE (idA INT, numA INT)
DECLARE @table2 AS TABLE (idB INT, numB INT)

INSERT INTO @table1
VALUES 
(NULL, 8),
(1, 10),
(2, 15),
(3, 16)

INSERT INTO @table2
VALUES
(2, 14),
(3, 30),
(4, 32)


SELECT COALESCE(ida, idb) AS id, ISNULL(numa, 0) AS numa, ISNULL(numb, 0) AS numb
FROM @table1
FULL OUTER JOIN @table2 ON ida = idb

回答3:

Is this what you're after?

select tableA.idA as Id, tableA.numA as numA, tableB.numB as numB
from tableA
inner join tableB on tableA.Id = tableB.Id