How can I improve this SQL query?

Question

I'm checking for existing of a row in in_fmd, and the ISBN I look up can be the ISBN parameter, or another ISBN in a cross-number table that may or may not have a row.

select count(*)
from in_fmd i
where (description='GN')
    and ( i.isbn in
    (
        select bwi_isbn from bw_isbn where orig_isbn = ?
        union all
        select cast(? as varchar) as isbn
    )
)

I don't actually care about the count of the rows, but rather mere existence of at least one row.

This used to be three separate queries, and I squashed it into one, but I think there's room for more improvement. It's PostgreSQL 8.1, if it matters.

Answer 1

Why bother with the UNION ALL

select count(*)
from in_fmd i
where (description='GN')
    and (
        i.isbn in (
            select bwi_isbn from bw_isbn where orig_isbn = ?
        )
        or i.isbn = cast(? as varchar)
    )

I would probably use a LEFT JOIN-style query instead of the IN, but that's more personal preference:

select count(*)
from in_fmd i
left join bw_isbn
    on bw_isbn.bwi_isbn = i.isbn
    and bw_isbn.orig_isbn = ?
where (i.description='GN')
    and (
        bw_isbn.bwi_isbn is not null
        or i.isbn = cast(? as varchar)
    )

The inversion discussed over IM:

SELECT SUM(ct)
FROM (
    select count(*) as ct
    from in_fmd i
    inner join bw_isbn
        on bw_isbn.bwi_isbn = i.isbn
        and bw_isbn.orig_isbn = ?
        and i.isbn <> cast(? as varchar)
        and i.description = 'GN'

    UNION

    select count(*) as ct
    from in_fmd i
    where i.isbn = cast(? as varchar)
        and i.description = 'GN'
) AS x

Answer 2

select count(*) from in_fmd i where description = 'GN' and exists (select 1 from bwi_isbn where bw_isbn.bwi_isbn = in_fmd.isbn)

Answer 3

I don't actually care about the count of the rows, but rather mere existence of at least one row.

Then how about querying SELECT ... LIMIT 1 and checking in the calling program whether you get one result row or not?

Answer 4

SELECT SUM(ct) FROM ( select count(*) as ct from in_fmd i inner join bw_isbn on bw_isbn.bwi_isbn = i.isbn and bw_isbn.orig_isbn = ? and i.isbn <> cast(? as varchar) and i.description = 'GN'

UNION

select count(*) as ct
from in_fmd i
where i.isbn = cast(? as varchar)
    and i.description = 'GN'

) AS x

Answer 5

apart from what other posters have noted , just changing

select count(*)

to

exists(..)

would improve things quite a bit