>

>SELECT Client.Numero
> FROM Client
> WHERE Client.NoProvince IN (SELECT Province.Numero FROM Province WHERE Client.NoProvince=Province.Numero AND Province.Active=1)
>

>

I've been shaking my head a little bit on this - not on the actual question and Martina's response, but on something else.

Unless I'm really missing something with the table structure, I don't believe the inner WHERE clause check on the province number (WHERE Client.NoProvince=Province.Numero ) is necessary. The outer WHERE with the IN is already doing the filtering.

What you're doing isn't harmful, but it isn't necessary. I'll bet if you remove the inner check and write it like this, you'll get the same results:

>

>SELECT Client.Numero
> FROM Client
> WHERE Client.NoProvince IN (SELECT Province.Numero FROM Province WHERE Province.Active=1)
>

>


Most times when people use IN, they use it with a subquery that isn't written as a correlated subquery.

Again, what you have works. (Admittedly, when I read it, I wondered if it would generate an error, because I assumed...dangerous world....that IN would not work with subqueries that referenced a table from the outer query).

If you were worried about NULL values in the Province table, then EXISTS might be better (EXISTS certainly works better if you have to search on multiple columns)

Maybe Naomi or Sergey or someone else who has run into this can jump in - admittedly this was the first time I'd ever seen an IN with a subquery written as a correlated subquery. It seemed a bit odd and I was actually surprised it worked. But maybe I'm missing something.