Re: [HACKERS] isnull() or is it?

> Right now I'm trying to do something that 'Practical SQL' seems to
> think is normal yet brings up the questions from earlier
> conversations. I'm querying two tables where
> if the second table (specialprice) has nothing that matches sku, I
> don't care.

>From your example query, it seems that you want to substitute 0.0 for
the "specialprice" field if an entry corresponding to a row in the first
table does not exist. That matches up with the capabilities of a left
outer join, where you would use CASE or COALESCE() to substitute the
zero for a null. Postgres does not have outer joins yet.

Your example does *not* give you the effect you want, since the inner
join you are specifying will not match up non-existent rows. btw,
neither of my reference/tutorial books mentions ifnull() (or at least
they don't have an index entry for it). I'm looking at "A Guide to the
SQL Standard", Date and Darwen, and "Understanding the New SQL", Melton
and Simon.

In the meantime I think you can mimic the effect using a union:

select products.image, products.sizes, products.colors,
products.weight, products.category, products.accessories,
products.saleprice, products.ourcost, products.description,
specialprice.specialprice
from products,specialprice
where (products.sku = '28434')
and (products.sku = specialprice.sku)
union
select products.image, products.sizes, products.colors,
products.weight, products.category, products.accessories,
products.saleprice, products.ourcost, products.description,
0.0
from products
where products.sku = '28434'
and (products.sku not in (select sku from specialprice);

That last clause could just have the constant rather than products.sku.

Postgres does have ISNULL in the parser, but that is just equivalent to
IS NULL.

Good luck.

- Tom