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

Question

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?

http://stackoverflow.com/questions/14052596/subquery-v-s-inner-join-in-sql-server — I_am_Batman, May 01 '16 at 18:30
It depends on what types of optimizations each of the dbs have implemented and how they implemented it. What works in MySQL might or might not work in sql server, and vice versa — Tudor Constantin, May 01 '16 at 18:40

score 3 · Answer 1 · answered May 01 '16 at 19:35

3

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

a very long time ago MySQL joins used to be slow

answered May 01 '16 at 19:35

Emil Perhinschi

1,185
11
14

I doubt that suqueries were faster back then – Tudor Constantin May 01 '16 at 21:39
joins were so slow that it was faster to do one query, gather the IDs then do another query with " IN (?,?,?,?) " no longer true, fortunately – Emil Perhinschi May 13 '16 at 20:23

score 2 · Answer 2 · answered May 01 '16 at 20:33

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.

score 1 · Accepted Answer · answered May 01 '16 at 18:27

1

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

answered May 01 '16 at 18:27

Tudor Constantin

26,330
7
49
72

As long as \`ID\` column is guaranteed to be unique in \`Orders\` table.., – spencer7593 May 01 '16 at 20:36

score 1 · Answer 4 · answered May 01 '16 at 19:40

1

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.

answered May 01 '16 at 19:40

dnoeth

59,503
4
39
56

Without the WHERE condition the first query will probably throw an error. – Paul Spiegel May 01 '16 at 20:09
@PaulSpiegel: Correct, must switch to `IN` instead. – dnoeth May 01 '16 at 20:10

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

4 Answers4