CODEWITHSUNDEEP
sqlpostgresql
Andy Lester
Andy Lester

Reputation: 93636

How can I improve this SQL query?

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.

Upvotes: 1

Views: 516

Answers (5)

Steve
Steve

Reputation: 

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

Upvotes: 0

anil
anil

Reputation: 

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

Upvotes: 1

Learning
Learning

Reputation: 8175

apart from what other posters have noted , just changing

select count(*)

to

exists(..)

would improve things quite a bit

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89651

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

Upvotes: 4

Jouni K. Sepp&#228;nen
Jouni K. Sepp&#228;nen

Reputation: 44118

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?

Upvotes: 1

Related Questions