CODEWITHSUNDEEP
mysqlsqloracle-database
VB_
VB_

Reputation: 45712

SQL: difference between INNER JOIN and INNER SELECT in particular case

I start to learn SQL. And I find that we often can achieve the same result with help of JOINs or Inner Select statements.

Question1 (broad): Where JOINs are faster than inner selects and vise versa?

Question2 (narrow): Can you explain me what causes performance difference of three queries below?

P.S. There is very nice site which calculates query performance, but I can't understand it estimation results.

Query1:

SELECT DISTINCT maker 
FROM Product pro INNER JOIN Printer pri
on pro.model = pri.model

Query2:

SELECT DISTINCT maker
FROM Product 
WHERE model IN (
  SELECT model FROM Printer
)

Query3:

SELECT distinct maker 
FROM Product pro, Printer pri 
WHERE pro.model = pri.model

Upvotes: 0

Views: 137

Answers (3)

Serpiton
Serpiton

Reputation: 3684

When the server evaluate a JOIN it matches the join equivalence scanning only the columns needed only for the value in the other table, and filter out everything else, it is usually done with a specific action.
When you have a subquery the server need to evaluate the plan for the subquery before the JOIN equivalence match, so if the subquery doesn't make up for the extra effort filtering out a lot of noise you have a better perfomance without it.

The server are quite smart, and they try to shave everything they don't need to evaluate the join. Then they try to use every index they can to have the best performance, where the best performance mean the best they can find in a limited amount of time, so that the plan time itself don't kill the performance.

Added after the comment of the OP
The O(n) estimation depent on the complexity of the query and the subquery, if you are interested on the query plan building you'll have to navigate the help section of your database of choice and probably you will not find a lot, if the DB is not opensource.
In layman term:

  • a the simple join is evaluated on one level, the main query plan
  • a sub query is evaluated on two level, the subquery plan and the main query plan.

Some DB IDE can display a visual rappresentation of the total plan, that usually help to understand some of those point (I don't know if mySQL has that)

Upvotes: 2

VIGNESH
VIGNESH

Reputation: 9

Normally INNER JOIN is to join two different table values ,where as INNER SELECT is to select a particular value from a different table and use the result to produce a single output.

Upvotes: 1

StanislavL
StanislavL

Reputation: 57421

Query1 is faster in general but RDBMC could optimize the Query2 to provide approximately the same result.

If the IN subquery rather complicated with dependencies from main table(s) it could be executed for each row retrieved to check the condition.

Upvotes: 1

Related Questions