有没有办法在 SQL 中连接两个表而不是在索引行上而不去除空值 [英] Is there way to join two tables in SQL not on you index row without getting rid of null values

问题描述

我在 SQL 中有两个表，它们看起来类似于以下内容:

I have two tables in SQL that look similar to the following:

Code        Symbol       Value
1203        ABC          10.00
1208        XYZ          12.00
1222        null         9.00
1226        ABC          1.00

和

Symbol       Date
ABC          2020-06-07
XYZ          2020-06-08
QRS          2020-06-10

目前，我正在尝试按以下方式加入他们

Currently, I am trying to join them as follows

SELECT a.Code, a.Symbol, a.Value, b.Date
FROM table1 a
LEFT JOIN table2 b ON a.Symbol = b.Symbol

这将返回以下输出:

Code        Symbol       Value          Date
1203        ABC          10.00          2020-06-07
1208        XYZ          12.00          2020-06-08
1226        ABC          1.00           2020-06-07

但是，我仍然希望保留初始表中的所有行，并将缺失值填充为空值:

However, I would like to still keep all rows from the initial table as such and fill in the missing values as null as such:

Code        Symbol       Value          Date
1203        ABC          10.00          2020-06-07
1208        XYZ          12.00          2020-06-08
1222        null         9.00           null
1226        ABC          1.00           2020-06-07

我知道这可能很简单，但我尝试过研究它，我想我脑抽筋了，因为我无法正确处理我的问题来找到我需要的东西.

I know this is likely quite straightforward but I have tried researching it and I think I am having a brain cramp because I can't work my issue correctly to find what I need.

感谢您的帮助.

推荐答案

我同意 @Gordon Linoff 的观点，您的表格可能以相反的顺序排列.要么翻转表格，要么使用 RIGHT JOIN 代替.

听起来你想要一个 LEFT (OUTER) JOIN.有关一般概述，请参阅此处.

Sounds like you want a LEFT (OUTER) JOIN. For a general overiew, see here.

WITH a AS (
    SELECT *
    FROM (VALUES (1203, 'ABC', 10.00),
                 (1208, 'XYZ', 12.00),
                 (1222, null, 9.00),
                 (1226, 'ABC', 1.00)
         ) as a (code, symbol, value)
), b AS (
    SELECT *
    FROM (VALUES ('ABC', '2020-06-07'::date),
                 ('XYZ', '2020-06-08'::date),
                 ('QRS', '2020-06-10'::date)
         ) as b (symbol, date)
)
SELECT code, a.symbol, value, date
FROM a LEFT JOIN b ON a.symbol = b.symbol

+----+------+-----+----------+
|code|symbol|value|date      |
+----+------+-----+----------+
|1203|ABC   |10   |2020-06-07|
|1208|XYZ   |12   |2020-06-08|
|1222|NULL  |9    |NULL      |
|1226|ABC   |1    |2020-06-07|
+----+------+-----+----------+

这篇关于有没有办法在 SQL 中连接两个表而不是在索引行上而不去除空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！