如何改进此SQL查询？

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.

我正在检查in_fmd中是否存在一行,而我查找的ISBN可以是ISBN参数,也可以是交叉数字表中可能有也可能没有行的另一个ISBN。

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.

这曾经是三个独立的查询,我把它压成一个,但我认为还有进一步改进的余地。它是PostgreSQL 8.1,如果重要的话。

5 个解决方案

#1

Why bother with the UNION ALL

为什么要打扰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:

我可能会使用LEFT JOIN风格的查询而不是IN,但这更具个人偏好:

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:

通过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

#2

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?

然后如何查询SELECT ... LIMIT 1并检查调用程序是否得到一个结果行?

#3

apart from what other posters have noted , just changing

除了其他海报所说的,只是改变

select count(*)

exists(..)

would improve things quite a bit

会改善一些事情

#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

#5

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

#1