CODEWITHSUNDEEP
mysqlsqljoinsubquery
porton
porton

Reputation: 5801

Weird SQL code: Why do they use a subquery instead of join?

I met the following MySQL code:

SELECT ServiceFee
FROM Shows
WHERE ID = (SELECT ShowID FROM Orders WHERE ID = ?)

It makes me wonder because the people who wrote this code usually use SQL joins. I would rewrite it

SELECT ServiceFee
FROM Shows
INNER JOIN Orders ON Shows.ID = Orders.ShowID
WHERE Orders.ID = ?

My question: Is there any reason why this code was written with a subquery and whether it is completely safe (producing the same result in all situations) to rewrite it with the join?

Are there any caveats?

Upvotes: 4

Views: 192

Answers (4)

Paul Spiegel
Paul Spiegel

Reputation: 31812

Without a context the two queries don't do the same. If Orders.ID is not unique, the first query might produce an error because the subquery can return multiple rows. If Orders.ID is unique (probably primary key) then there will not be any difference - neither in the result nor in the performance (with newer versions). So in this case it is just a question of preference. Usually i prefer joins. But in your example the subquery solution looks fine and readable. So i wouldn't rewrite it without a reason.

Upvotes: 2

dnoeth
dnoeth

Reputation: 60472

There is a logical reason to prefer a Subquery over a Join:

Between Shows and Orders there's a 1-many relation, when you switch to a join you usually must add a DISTINCT to get rid of the duplicate rows returned.

In your case this is not needed, because you restrict to a single order, but when you remove the WHERE-condition or change it to return multiple rows you will notice that.

Upvotes: 1

Emil Perhinschi
Emil Perhinschi

Reputation: 1283

"Is there any reason why this code was written with a subquery"

a very long time ago MySQL joins used to be slow

Upvotes: 3

Tudor Constantin
Tudor Constantin

Reputation: 26861

Nope, there are no caveats. As a matter of fact, the INNER JOIN query might run faster

Upvotes: 1

Related Questions