Questions regarding 2 similar queries

[Boon]

Hi,

I am wondering what different between the following 2 methods. Both methods
yield the same result.

1. I did the select query to show a table A. And I had one criteria on field
Cus in table A that says "IN (SELECT tableB.Cus FROM tableB)"

2. I did the select query to show a table A. And I joined the field "Cus" in
table A with field "Cus" in table B.


It looks to me that the #2 run faster. Is there any reason why? And which
method is a best practice?

Thanks.
B

 

[Duane Hookom]

Different implementations of SQL are optimized for different syntaxes.
Generally the JOIN will be more efficient. I assume the Cus field is indexed
in both tables.

 

[Boon]

It is indexed. But what if it isn't? Should there be any error? The Cus
field in table B is a primary key though.

 

[Duane Hookom]

If the fields aren't indexed and you have lots of records, the query will
take longer to run.