SQL Joins Vs SQL Subqueries (Performance)?

Question

I wish to know if I have a join query something like this -

Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

and a subquery something like this -

Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

When I consider performance which of the two queries would be faster and why ?

Also is there a time when I should prefer one over the other?

Sorry if this is too trivial and asked before but I am confused about it. Also, it would be great if you guys can suggest me tools i should use to measure performance of two queries. Thanks a lot!

Solution 1

I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).

As with ALL THINGS SQL though, your mileage may vary. The speed will depend a lot on indexes (do you have indexes on both ID columns? That will help a lot...) among other things.

The only REAL way to tell with 100% certainty which is faster is to turn on performance tracking (IO Statistics is especially useful) and run them both. Make sure to clear your cache between runs!

Solution 2

Well, I believe it's an "Old but Gold" question. The answer is: "It depends!". The performances are such a delicate subject that it would be too much silly to say: "Never use subqueries, always join". In the following links, you'll find some basic best practices that I have found to be very helpful:

• Optimizing Subqueries
• Optimizing Subqueries with Semijoin Transformations
• Rewriting Subqueries as Joins

I have a table with 50000 elements, the result i was looking for was 739 elements.

My query at first was this:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND p.anno = (
    SELECT MAX(p2.anno) 
    FROM prodotto p2 
    WHERE p2.fixedId = p.fixedId 
)

and it took 7.9s to execute.

My query at last is this:

SELECT  p.id,
    p.fixedId,
    p.azienda_id,
    p.categoria_id,
    p.linea,
    p.tipo,
    p.nome
FROM prodotto p
WHERE p.azienda_id = 2699 AND (p.fixedId, p.anno) IN
(
    SELECT p2.fixedId, MAX(p2.anno)
    FROM prodotto p2
    WHERE p.azienda_id = p2.azienda_id
    GROUP BY p2.fixedId
)

and it took 0.0256s

Good SQL, good.

Solution 3

Performance is based on the amount of data you are executing on...

If it is less data around 20k. JOIN works better.

If the data is more like 100k+ then IN works better.

If you do not need the data from the other table, IN is good, But it is alwys better to go for EXISTS.

All these criterias I tested and the tables have proper indexes.

Solution 4

Start to look at the execution plans to see the differences in how the SQl Server will interpret them. You can also use Profiler to actually run the queries multiple times and get the differnce.

I would not expect these to be so horribly different, where you can get get real, large performance gains in using joins instead of subqueries is when you use correlated subqueries.

EXISTS is often better than either of these two and when you are talking left joins where you want to all records not in the left join table, then NOT EXISTS is often a much better choice.

Solution 5

The performance should be the same; it's much more important to have the correct indexes and clustering applied on your tables (there exist some good resources on that topic).

(Edited to reflect the updated question)

View more solutions

Comments

[Francois Botha]

@Lucero, this question is tagged sql-server-2008, where the post you mention is tagged MySql. You can infer that the answers will be the same. Performance optimisation is done differently on the two RDBMSs.

[Admin]

"an Exists filter would probably perform the fastest" - probably not, I think, although a definitive answer would require testing against the actual data. Exists filters are likely to be faster where there are multiple rows with the same lookup values - so an exists filter might run faster if the query was checking whether other employees had been recorded from the same department, but probably not when looking up against a department table.

[Snekse]

Would it run slower in that last scenario?

[Admin]

It would depend on the optimiser - under certain circumstances, it might, but normally I would expect very similar performance.

[Erik Hart]

I have serious doubt on this answer, since most DBMS, definitely SQL Server 2008 and later, translate the single ID subquery (not correlated, meaning: not referencing multiple outer query columns) into a relatively fast semi-join. Also, as previously noted in another answer, the first, real join will return a row for EACH occurence of the matching ID in Dept - this makes no difference for a unique ID, but will give you tons of duplicates elsewhere. Sorting these out with DISTINCT or GROUP BY will be another, heavy performance load. Check execution plans in SQL Server Management Studio!

[Erik Hart]

The IN clause as an equivalent to OR applies to parameter/value lists, but not to subqueries, which are mostly treated like joins.

[cozos]

Interesting, could you explain how adding the GROUP BY fixed it?

[Sirmyself]

The temporary table generated by the subquery was smaller. Therefore the execution is quicker since there are less data to check in.

[Ali Faradjpour]

I think that in first query you have shared variable between outer query and subquery, so for every row in main query, subquery executes but in second one the subquery only executes one time and this way performance improved.

[Alix]

Sql server and MySql and ...Sql (excepting NoSql) are so similar in infrastructure. We have a kind of query optimization engine underneath which converts the IN (...) clauses to join (if it was possible). But when you have a Group by on a well indexed column (based on its cardinality) then it will be much faster. So it really depends on the situation.

[Yuval Perelman]

are you sure the buffer was clean? it makes a lot of sense that if you ran both queries one after the other there would be a massive difference in performance