我有两个表,我想从这两个表的所有数据。如果表有匹配的AssetID,那么将它们连接在一行上。如果没有,则在单独的行上。一个完整的外部连接听起来像正确的方法,但我有一个问题,如何选择键取决于它来自哪个表。
TABLE A TABLE B
AssetID | Valuable AssetID | Protected
------------------- -------------------
123 | Yes 123 | Yes
456 | No 321 | No
653 | Yes
目标:
TABLE C
AssetID | Valuable | Protected
---------------------------
123 | Yes |Yes
456 | No |
653 | Yes |
321 | |No
SELECT TableA.AssetID, TableA.Valuable, TableB.Protected
FROM (
SELECT AssetID, Valuable
FROM TableA
) ta
FULL OUTER JOIN (
SELECT AssetID, Protected
FROM TableB
) tb ON ta.AssetID=tb.AssetID
生产
表C
AssetID | Valuable | Protected
---------------------------
123 | Yes |Yes
456 | No |
653 | Yes |
| |No <<<< PROBLEM
---------------------------
我错过了钥匙
您可以使用coalesce
从任何表中获取非空的assetID
:
SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM
(
SELECT
AssetID,
Valuable
FROM
TableA
) ta
FULL OUTER JOIN
(SELECT
AssetID,
Protected
FROM
TableB
) tb
ON ta.AssetID=tb.AssetID
注意:但是,您可能不需要子查询,省略它们可以大大简化查询:
SELECT COALESCE(TableA.AssetID, TableB.AssetID) TableA.Valuable, TableB.Protected
FROM TableA
FULL OUTER JOIN TableB
ON TableA.AssetID=TableB.AssetID
要处理Null值,我们使用Coalesce函数。或者,我们可以使用左连接和右连接来代替全连接。
SELECT COALESCE(TABLEA.AssetID,TABLEB.AssetId) AS
AssetId,TABLEA.Valuable,TABLEB.Protected
FROM TABLEA
LEFT JOIN
TABLEB ON TABLEA.AssetID = TABLEB.AssetID
UNION
SELECT COALESCE(TABLEA.AssetID,T1.AssetID) AS
AssetID,TABLEA.Valuable,T1.Protected FROM TABLEA
RIGHT JOIN
TABLEB T1 ON TABLEA.AssetID=T1.AssetID