CODEWITHSUNDEEP
sql
trinity
trinity

Reputation: 10484

SQL Queries comparison

Tables A and B in action.
A [commonfield, otherfields]
B [commonfield]

Query 1

select
        A.otherfields
from
        A,B
where
        A.commonfield = B.commonfield
        and some filters ( A.commonfield )

Query 2

select
        A.otherfields
from
        A
where
        A.commonfield in ( select B.commonfield from B )
        and some filters ( A.commonfield )

Query 1 is equivalent to Query 2. Which is better with regards to (a) memory usage and (b) speed ?

Upvotes: 3

Views: 69

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Query 1 is not equivalent to Query 2!

Query1 will return multiple rows when there are multiple matches in B. Query 2 will only return the rows in A, with no duplicates.

Since these do not return equivalent result sets, I don't think it is a good idea to compare their performance.

However, Query 1 will probably perform better, because it is not doing duplicate elimination. On the other hand, if you have appropriate indexes, then the two might be similar or Query 2 might even be better.

Upvotes: 0

pablosaraiva
pablosaraiva

Reputation: 2343

Those two queries will be compiled before execution.

The performance will be dependent of your SQL Server Vendor implementation of the query compiler.

One vendor may optimize both to the same code to be executed.

I'm using SQL Server 2000 and both expressions resulted in equivalent code with equivalent performance.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Another possiblity:

SELECT
        A.otherfields
FROM
        A
WHERE EXISTS (SELECT * FROM B WHERE A.commonfield = B.commonfield)
        AND some filters ( A.commonfield )

Where exists tend to be fastest in SQL Server but not in all databases.

I would test all possiblities (except the one using implicit joins which is a poor programming technique all by itself and should not be used unless you have such an old database that you can't use an explicit join)) to determine the best performance with your particualr database and setup.

Upvotes: 1

marc_s
marc_s

Reputation: 754468

The best and the ANSI Standard way of doing it would be

Query #3:

select
        A.otherfields
from
        A
inner join
        B ON A.commonfield = B.commonfield
where
        some filters ( A.commonfield )

Upvotes: 5

Related Questions