Posted by Branco Medeiros on 11/06/20 11:52

Hugo Kornelis wrote:
<snip>
> Best practice: use IN and NOT IN only wiith a list of literals. Never
> use either of them with a subquery. Apart from the problem with NULLs,
> there are other pproblems: they often don't perform well on SQL Server,
> and you can't extend the syntax for multi-column comparisons.
>
> Every [NOT] IN with a subquery can always be transformed to a [NOT]
> EXISTS with a subquery - and [NOT] EXISTS has no problems with NULL
> values in the subquery, supports multi-column comparisons and usually
> performs as good or better as [NOT] IN.
>
> SELECT r.COD_RUA
> FROM RUA AS r
> WHERE NOT EXISTS
> (SELECT *
> FROM CONSUMIDOR AS c
> WHERE c.COD_RUA = r.COD_RUA)


Thanks, Hugo!

That was fast and *extremely* cool.

As a side note, I really missed an idiom to verify the (non) existence
of items accross tables and it seems that the syntax of [NOT] EXISTS
that you suggest is exactly what I needed.

Thanks a lot.

Best regards,

Branco.

• Next in forum: Re: Query: union on self
• Prev in forum: Re: Query: union on self
• Thread view: Re: Strange results from "not in" query

[Reply to this message]