Join vs. sub-query
Solution 1
Taken from the MySQL manual (13.2.10.11 Rewriting Subqueries as Joins):
A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.
So subqueries can be slower than LEFT [OUTER] JOIN
, but in my opinion their strength is slightly higher readability.
Solution 2
Sub-queries are the logically correct way to solve problems of the form, "Get facts from A, conditional on facts from B". In such instances, it makes more logical sense to stick B in a sub-query than to do a join. It is also safer, in a practical sense, since you don't have to be cautious about getting duplicated facts from A due to multiple matches against B.
Practically speaking, however, the answer usually comes down to performance. Some optimisers suck lemons when given a join vs a sub-query, and some suck lemons the other way, and this is optimiser-specific, DBMS-version-specific and query-specific.
Historically, explicit joins usually win, hence the established wisdom that joins are better, but optimisers are getting better all the time, and so I prefer to write queries first in a logically coherent way, and then restructure if performance constraints warrant this.
Solution 3
In most cases JOIN
s are faster than sub-queries and it is very rare for a sub-query to be faster.
In JOIN
s RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.
The good thing in sub-queries is that they are more readable than JOIN
s: that's why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too.
Solution 4
In the year 2010 I would have joined the author of this questions and would have strongly voted for JOIN
, but with much more experience (especially in MySQL) I can state: Yes subqueries can be better. I've read multiple answers here; some stated subqueries are faster, but it lacked a good explanation. I hope I can provide one with this (very) late answer:
First of all, let me say the most important: There are different forms of sub-queries
And the second important statement: Size matters
If you use sub-queries, you should be aware of how the DB-Server executes the sub-query. Especially if the sub-query is evaluated once or for every row! On the other side, a modern DB-Server is able to optimize a lot. In some cases a subquery helps optimizing a query, but a newer version of the DB-Server might make the optimization obsolete.
Sub-queries in Select-Fields
SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo
Be aware that a sub-query is executed for every resulting row from foo
.
Avoid this if possible; it may drastically slow down your query on huge datasets. However, if the sub-query has no reference to foo
it can be optimized by the DB-server as static content and could be evaluated only once.
Sub-queries in the Where-statement
SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)
If you are lucky, the DB optimizes this internally into a JOIN
. If not, your query will become very, very slow on huge datasets because it will execute the sub-query for every row in foo
, not just the results like in the select-type.
Sub-queries in the Join-statement
SELECT moo, bar
FROM foo
LEFT JOIN (
SELECT MIN(bar), me FROM wilco GROUP BY me
) ON moo = me
This is interesting. We combine JOIN
with a sub-query. And here we get the real strength of sub-queries. Imagine a dataset with millions of rows in wilco
but only a few distinct me
. Instead of joining against a huge table, we have now a smaller temporary table to join against. This can result in much faster queries depending on database size. You can have the same effect with CREATE TEMPORARY TABLE ...
and INSERT INTO ... SELECT ...
, which might provide better readability on very complex queries (but can lock datasets in a repeatable read isolation level).
Nested sub-queries
SELECT VARIANCE(moo)
FROM (
SELECT moo, CONCAT(roger, wilco) AS bar
FROM foo
HAVING bar LIKE 'SpaceQ%'
) AS temp_foo
GROUP BY moo
You can nest sub-queries in multiple levels. This can help on huge datasets if you have to group or change the results. Usually the DB-Server creates a temporary table for this, but sometimes you do not need some operations on the whole table, only on the resultset. This might provide a much better performance depending on the size of the table.
Conclusion
Sub-queries are no replacement for a JOIN
and you should not use them like this (although possible). In my humble opinion, the correct use of a sub-query is the use as a quick replacement of CREATE TEMPORARY TABLE ...
. A good sub-query reduces a dataset in a way you cannot accomplish in an ON
statement of a JOIN
. If a sub-query has one of the keywords GROUP BY
or DISTINCT
and is preferably not situated in the select fields or the where statement, then it might improve performance a lot.
Solution 5
Use EXPLAIN to see how your database executes the query on your data. There is a huge "it depends" in this answer...
PostgreSQL can rewrite a subquery to a join or a join to a subquery when it thinks one is faster than the other. It all depends on the data, indexes, correlation, amount of data, query, etc.
Your Common Sense
Wasting my life trying to mitigate the damage Stack Overflow does to PHP community
Updated on July 08, 2022Comments
-
Your Common Sense almost 2 years
I am an old-school MySQL user and have always preferred
JOIN
over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why.I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a
JOIN
and therefore is there nothing to worry about?-
runrig almost 14 yearsSubqueries are great sometimes. They suck performance-wise in MySQL. Don't use them.
-
Dejan almost 14 yearsI was always under the impression that sub-queries implicitly were executed as joins where available in certain DB technologies.
-
ovais.tariq almost 14 yearsSub queries don't always suck, when joining with pretty large tables, the preferred way is to do a sub-select from that large table (limiting the number of rows) and then joining.
-
Piskvor left the building over 13 years"nowadays everyone uses sub-query" [citation needed]
-
Daisy Leigh Brenecki about 13 yearsPotentially related (although much more specific): stackoverflow.com/questions/141278/subqueries-vs-joins/…
-
spaaarky21 almost 10 years@Piskvor Considering that MySQL didn't support subqueries until 4.1, to an "old-school MySQL user", seeing just a few people using subqueries must seem like "everyone". :)
-
guettli almost 8 yearsWhy do you "hate" sub-queries?
-
Starx over 7 years@runrig, Is your comment still true now?
-
runrig over 7 years@Starx - I have no idea...haven't used mysql for years.
-
Starx over 7 years@runrig, Oh.. Thanks anyways. What db technology do you use then?
-
UncaAlby about 7 years@Starx, I can attest to the fact that, yes, it is still true. I have a sub-query on a couple of monster tables that took all night before I killed the processes. The Join completed in 0.93 seconds.
-
-
Álvaro González about 14 yearsGreat answer. I'd also add that developers (esp. amateur ones) are not always proficient in SQL.
-
Cine almost 14 yearsYes, most databases therefore includes it as an optimization step to convert subqueries into joins when it is analyzing your query.
-
Unreason almost 14 yearsThis answer is a bit too simplified for the question that was asked. As you state: certain subqueries are ok and certain are not. The answer does not really help to distinguish the two. (also the 'very rare' really depends on your data/app).
-
Book Of Zeus over 12 years@Kronass you are totally right, although, i think JOINS are easier to read when they are well formated.
-
bool.dev over 12 yearsi'd like to know more about what you are trying to point out in the last line "My guess is that DISTINCT mos_content.catid takes far longer to figure out than DISTINCT mos_categories.id does." . Are you saying that an id should be named only
id
and not named something likecatid
? Trying to optimize my db accesses, and your learnings could help. -
Uğur Gümüşhan over 12 yearscan you prove any of your points with documentation reference or test results?
-
zuloo over 12 yearsI made very good experiences with sub-queries that contain a back-reference to the upper query, especially when it comes to row-counts above 100,000. The thing seems to be memory usage and paging to the swap-file. A join would produce a very big amount of data, that may not fit into memory and must be paged into the swap-file. Whenever this is the case the query-times of small sub-selects like
select * from a where a.x = (select b.x form b where b.id = a.id)
is extremely small compared to a join. This is a very specific problem, but in some cases it brings you from hours to minutes. -
Amir Pashazadeh almost 12 yearsI'm experienced with Oracle and I can say, sub-queries are much better on large tables if you don't have any filtering or sorting on them.
-
Frank Schmitt over 11 yearsReplacing subqueries with functions is a very bad idea performance-wise in some RDBMS (e.g. Oracle), so I'd recommend just the opposite - use subqueries/joins instead of functions wherever possible.
-
Uğur Gümüşhan over 11 years@FrankSchmitt please support your argument with references.
-
Uğur Gümüşhan about 11 yearsusing SQL IN in that case is a bad practice and it doesn't prove anything.
-
Tim Schmelter over 10 yearsThere are also cases where you should use a sub query instead of a join even if you check for existence: if you check for
NOT EXISTS
. ANOT EXISTS
wins over aLEFT OUTER JOIN
for various reasons: preformance, fail-safety (in case of nulable columns) and readability. sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join -
WojonsTech about 10 yearsthis is exactly why postgresql is so good and useful it understands what the goal is and will fix a query based on what it think is better and postgresql is very good at knowing how to look at its data
-
pahnin about 10 yearsI have similar situation, but with more joins than yours, will try with explain once
-
cleong almost 10 yearsI've similar experience with SQLServer. Sub-queries are generally much faster.
-
wobbily_col over 9 yearsI have read that MySQL specifically doesn't handle nested subqueries well. Postgres is apparently better.
-
David Aldridge almost 9 yearsIn Oracle or PostgreSQL I would have tried: AND NOT EXISTS (SELECT 1 FROM list_tag WHERE list_id=l.list_id AND tag_id in (43, 55, 246403))
-
Ali Umair over 8 years+1 Looking for some logical explanation for this issue for a long time, this is only answer that seems logical to me
-
Ciro Santilli OurBigBook.com almost 8 yearsAnother case: multiple
GROUP BY
s with different tables: stackoverflow.com/questions/11415284/… Subqueries seem to be strictly more general. See also the MySQL man: dev.mysql.com/doc/refman/5.7/en/optimizing-subqueries.html | dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html -
David Harkness almost 8 years-1 This is misleading as you're using a subquery and join in both examples. That you've pulled the subquery out into a second query to determine the lowest order price has no effect since the database will do the exact same thing. Plus, you're not rewriting the join using a subquery; both queries use a join. You are correct that subqueries allow aggregate functions, but this example doesn't demonstrate that fact.
-
Jinghui Niu over 7 years@Marcelo Cantos, Could you please give an example of your statement "It is also safer, in a practical sense, since you don't have to be cautious about getting duplicated facts from A due to multiple matches against B."? I found this very insightful yet a little too abstract. Thanks.
-
Alex S over 7 years@JinghuiNiu Customers who bought expensive items:
select custid from cust join bought using (custid) where price > 500
. If a customer bought multiple expensive items, you'll get double-ups. To fix this,select custid from cust where exists (select * from bought where custid = cust.custid and price > 500)
. You could useselect distinct …
instead, but it's often more work, either for the optimizer or the evaluator. -
Daniel Shin about 7 yearsheww. I guess no need to re-write tons of queries for me! postgresql for the win.
-
MatTheWhale over 6 yearsDo you even need to do a join in your example though? Wouldn't
SELECT custid FROM bought WHERE price > 500
suffice? Sorry if this seems pedantic, but your example is confusing me a little -
Alex S over 6 years@MatTheWhale yes I used an oversimplified answer coz I was lazy. In a real scenario you would be pulling more columns than just custid from cust.
-
user1735921 over 6 yearsI agree, sometimes breaking the query also works, when you have million records, you don't want to use joins because they take for ever. Rather handle it in the code and map in the code is better.
-
simhumileco over 6 years@user1735921 IMO it depends ... Generally, it's very important the readability of the code, because it is of great importance for later management of it... Let's remember the famous statement of Donald Knuth: "Premature optimization is the root of all evil (or at least most of it) in programming". However, naturally there are programming areas where performance is paramount... Ideally, when one succeeds in reconciling one with another :)
-
Zeek2 over 6 yearsI was told to prefer joins and leave to the optimization to SQL Server as SQL Server architects spend much of their time working on ways to optimize joins. HOWEVER, in practice I found that approach often yielded terrible results and, IMHO, it is an abdication of responsibility. Joins have their own special dangers and risks which are too often ignored (until reality bites). Each approach has strengths and weaknesses; experiment, measurement and experience will ultimately be your best guide for any give situation.
-
Zahra over 6 yearsIn more complex queries, I find joins much easier to read than sub-queries. sub-queries turn into a bowl of noodles in my head.
-
Zahra over 6 yearscan you provide an example of a query written using sub-queries that can not be converted to joins (second class, as you call it)?
-
fabio.sussetto over 6 years@user1735921 sure, especially when the query gets so complicated that it does the wrong thing and you spend a day fixing it... there's a balance in between, as usual.
-
digital.aaron over 6 yearsTie your joins aren't working fast enough, you might be missing an index. Query Analyzer can be pretty helpful in comparing actual performance.
-
user1735921 over 6 yearsJoins have higher readability as well as performance but if you are using way too many joins (like 5-10) in the query it would better to think of some optimization and breaking into two queries or sub queries which will increase performance as well as readabilty, thats what I learnt from my experience.
-
user1735921 over 6 yearsI agree with Ajay Gajera, I have seen this for myself.
-
user1735921 over 6 yearsI agree with David, and you can use group by to get the minimum price.
-
user10089632 over 6 yearsEternal dilemma between writing code for machine versus for humans. Some would argue don't worry about performance until it is an issue, I'd add : test test test the claim that joins are significantly faster than sub-queries
-
Alex S over 6 years@user10089632 agreed but it's only a dilemma historically because SQL engines sucked at optimisations. It's not entirely the fault of the authors insomuch as SQL semantics make it much more difficult to rewrite expressions than it would be in a properly relational engine. Modern SQL engines are much better at this, but it has been a long hard road to get here, and there's still a long way to go, imo.
-
Joshua Schlichting about 6 years@user1735921 Only if the performance gains are worth the increase in maintenance time required in the future
-
JDS about 6 yearsThis answer was written 8 years ago. Is this still true today (as of March 1 2018)?
-
Andrei about 6 yearsI did this mistake in the past and some people i know keep making this mistake with Join: SELECT SUM(A.Something) AS SumSomething, ... FROM A LEFT JOIN B ON B.A_ID = A.ID So yes, subqueries are safer for non-superprofessionals.
-
Saurabh Patil about 6 yearsI think you can't use a sub-query here. This isn't a case where you logically can use either but one gives a wrong answer because of the technical implementation of it. This is a case where you CANNOT use a sub-query because a student not belonging to CS can score 3.9 which is in the IN list of scores. The context of CS is lost once sub-query is executed,which isn't what we want logically. So this isn't a good example where either can be used. Usage of sub-query is conceptually/logically wrong for this use-case even if luckily it gives right result for a different dataset.
-
Paul Spiegel about 6 yearsHow does it make any sense to compare the performance of two queries which return different results?
-
Thavaprakash Swaminathan almost 6 yearsMy opinion
Join
andsub query
has different syntax, so readability we can not compare, both have higher readability as long as you are good in SQL syntax. Performance is more important. -
ANIK ISLAM SHOJIB almost 6 yearsYes those are different queries but returning same result
-
tuxayo over 5 years@Cine Which DBMSs can do such an optimization?
-
Cine over 5 years@tuxayo Check the query plan for your specific query to see if your database has done the right optimization. It depends on more factors than just the DBMS supporting it, as some of the other answers states.
-
Gustavo Emmel about 5 yearstrue story, join is faster than subquery
-
pilat about 5 years> but this statement applies to simple cases I understand that it's either a simple case that can be rewritten to "JOIN" by RDBMS, or it's such a complex case that subqueries are appropriate here. :-) Nice point on ORMs. I think this has the greatest impact.
-
jxc about 5 yearsFor
Sub-queries in the Join-statement
: (1) generating derived table from the sub-query itself could take a very long time. (2) the resulting derived table is not indexed. these two alone could significantly slow down the SQL. -
Trendfischer about 5 years@jxc I can only speak for MySQL (1) There it is a temporary table similar to a join. The time depends on the amount of data. If you cannot reduce the data with a subquery, use a join. (2) This is right, it depends on the factor you can reduce the data in the temporary table. I had real world cases, where I could reduce the join size form some millions to a few hundred and reducing the query time from multiple seconds (with full index usage) to a quarter of a second with a subquery.
-
jxc about 5 yearsIMO: (1) such temporary table (derived table) is not materialized, thus each time you run the SQL, the temporary table must be recreated, that could be very costly and a real bottle-neck (i.e. running a group by on millions of records) (2) even if you can reduce the size of temp table to
10
records, since there is no index, that still means potentially to query 9 times more data records than w/o the temp table when JOINing other tables. BTW I had this issue before with my db(MySQL), in my case, using sub-query inSELECT list
could be much faster. -
Trendfischer about 5 years@jxc I do not doubt that there are a lot of examples, where using a subquery is less optimal. As good practice you should use
EXPLAIN
on a query before optimizing. With the oldset profiling=1
you could easily see, if a temporary table is a bottleneck. And even an index needs processing time, B-Trees optimize querying for records, but a 10 record table can be much faster than an index for millions of records. But it depends on multiple factors like field sizes and types. -
jxc about 5 yearsUsing
EXPLAIN
should be enough to see how the DERIVED table(created from sub-query in the FROM list) could impact the query. I use sub-queries a lot at work, just try to mention thatsub-queries in the Join-statement
is probably not as promising as you think. the reduced records from the temporary table could have a much bigger cost than its gain. Also remember, even if the final JOINs could take less time, the time to scan millions of records in the sub-query remain and need to count for each SQL run. -
Trendfischer about 5 years@jxc Perhaps our different experience results in the different domains we do our queries. Sub-queries in the Join-statement are certainly promising, but perhaps I missed the real key constraint which is essential for the huge performance gain I get. Perhaps the aggregation functions like
MIN()
are the tipping point which result in a slightly different join strategy, though I already state that aggregation is a key factor for using sub queries. -
Bevelopper about 4 yearsLast year I started working with Postgre. I started a query with a join and for some reason I reworked it as a sub-query and I showed the result to my colleague because I was very surprised. The sub query one was much faster.
-
LearnByReading about 4 years@MarceloCantos great answer, but I wish that you had provided a concrete example to illustrate what is meant by "Get facts from A, conditional on facts from B". YOu mean like when the WHERE conditions applies to both tables? Sorry I am not able to connect abstract to example
-
Alex S about 4 years@LearnByReading An example might be when A is customers, B is accounts, and you want all customers that have no accounts with a balance over $1000.
-
Trendfischer almost 4 yearsThis depends on the SQL server and on the complexity of the query. A lot of SQL implementations would optimize simple queries like this for the best performance. Perhaps provide an example server name and version where this behavior happens to improve the answer?
-
Wolf about 3 years@anikislamshojib take a closer look: we have
*
and two tables in the first statement but only one table in the second statement, so I would expect a difference in the column count. -
SGiux about 3 yearsGood explanation but I'm not agree with the last example. Since GROUP BY and HAVING commands are executed before the ORDER BY command by the optimizer, this sub-query should not improve the performance of the query.
-
Trendfischer almost 3 years@SimoneG Thanks, you are right. I changed the example. Did not thought on execution ordering on creating the former shortened example. Hopefully the new example is correct. Better ideas are welcome.
-
Qiao over 2 yearssub-query is really much faster than joins if table has big data (say millions). Some ppl treat sub-query as anti-pattern but please, speed is one of the most important factors in majority cases.
-
hukeping over 2 yearsI've got a
left join
much slower thansub query
, so I don't think it will really do the job. -
grofte about 2 yearsA lot of introductions to SQL also include the blanket statement that "joins are slow / computationally expensive" without including that subqueries often are much slower. The important thing is that subqueries and joins are not equivalent and you should try both ways.