sql - Postgres Full Outer Join Headscratcher

如果我写这样的查询：

with foo as (
    select
        'foo' as name,
        1 as value
    union all select
        'foo', 2
    union all select
        'foo', 3
    union all select
        'foo', 4
), bar as (
    select
        'bar' as name,
        3 as value
    union all select
        'bar', 4
    union all select
        'bar', 5
    union all select
        'bar', 6
)
select
    f.name,
    b.value,
    b.name
from
    foo as f
full outer join
    bar as b
    on b.value = f.value;

我明白了：

name        value    name
foo         (null)  (null)
foo         (null)  (null)
foo         3       bar
foo         4       bar
(null)      5       bar
(null)      6       bar

这就是我所期望的。但是，如果我尝试对表值函数执行类似的操作，例如：

with allSizes as (
        select cast('120X60' as character varying) as size
        union all select '160X600'
        union all select '1X1'
        union all select '300X250'
        union all select '728X90'
        union all select '88X32'
        union all select 'PEEL'
)
select
        msd.region,
        msd.market,
        s.size,
        msd.target,
        msd.actual
from
        marketdata('2013-01-05') as msd
full outer join
        allSizes as s
        on s.size = msd.size
where
        msd.market = 'Foo, USA';

其中marketdata()是一个表值函数，我没有得到与size函数中不存在的marketdata()列对应的空行。为什么不呢？

最佳答案

这是因为：

where
        msd.market = 'Foo, USA';

它生成full outer join，然后过滤掉marketdata()中不存在的行（因为它们具有msd.market=NULL）
您可以将查询更改为：

with allSizes as (
        select cast('120X60' as character varying) as size
        union all select '160X600'
        union all select '1X1'
        union all select '300X250'
        union all select '728X90'
        union all select '88X32'
        union all select 'PEEL'
)
select
        msd.region,
        msd.market,
        s.size,
        msd.target,
        msd.actual
from
        (select marketdata('2013-01-05')
         where  market = 'Foo, USA') as msd
full outer join
        allSizes as s
        on s.size = msd.size;

或者干脆重写

where
        msd.market = 'Foo, USA' OR msd.market IS NULL;

如果marketdata()不返回带有msd.market IS NULL的行。

关于sql - Postgres Full Outer Join Headscratcher，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14219841/

MarketData

sql - Postgres Full Outer Join Headscratcher