Subject Re: [firebird-support] How use IN with many field?
Author Helen Borrie
At 03:10 PM 23/02/2007, you wrote:
>I want to write a following query
> select *
>from A
>where a.field1, a.field2
>(select B.field1, B.field2
>from B)

You can't use IN() like that. It is for comparing one value with a
set of values.

Take care with table identifiers.

You can do this query as

select ax.* from A ax
where exists (
select 1 from B bx
where bx.field1 = ax.field1
and bx.field2 =ax.field2)

But an inner join may be less costly than the correlated existence query:

from A ax join B bx
on bx.field1 = ax.field1
and bx.field2 = ax.field2

Tip: don't use select * at all. It was invented as a quick way for
lazy idiots to leave their signature in client applications.