Обсуждение: Can i customize null-padding for outer joins?

<span class="postbody">Hello, <br /><br />I have a query where I full-outer-join 2 tables, and all the columns other
thanthe join column are numerical columns. For my further calculations i need to pad the unmatched tuples with 0 (zero)
insteadof NULL so that I can perform meaningful mathematical calculations on them. Is this currently possible? <br
/><br/>Thanks a ton!<br />Shruthi<br /></span> 

On Thu, Oct 1, 2009 at 12:19 AM, Shruthi A <shruthi.iisc@gmail.com> wrote:
> Hello,
>
> I have a query where I full-outer-join 2 tables, and all the columns other
> than the join column are numerical columns. For my further calculations i
> need to pad the unmatched tuples with 0 (zero) instead of NULL so that I can
> perform meaningful mathematical calculations on them. Is this currently
> possible?

something like

select a.x, coalesce(b.y,0) from a left join b yada...

work?

In response to Shruthi A :
> Hello,
> 
> I have a query where I full-outer-join 2 tables, and all the columns other than
> the join column are numerical columns. For my further calculations i need to
> pad the unmatched tuples with 0 (zero) instead of NULL so that I can perform
> meaningful mathematical calculations on them. Is this currently possible?

You can use COALESCE() in your numerical calculation, for instance:

test=*# create table foo (n numeric);
CREATE TABLE
test=*# insert into foo values (1),(NULL),(NULL);
INSERT 0 3
test=*# select avg(n) from foo;         avg
------------------------1.00000000000000000000
(1 row)

test=*# select avg(coalesce(n,0)) from foo;         avg
------------------------0.33333333333333333333
(1 row)


Or use coalesce in your join-statement:

select coalesce(column,0) ...


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)