You don't always need JOINs 

Inner joins are more difficult to optimize because the search space of possible execution plans is greater. But the availability of more options also means that the best possible plan is potentially better than with a left join. So if you're in a situation where both will work semantically, it makes sense to profile both variants.

That's all nice and fine, until you start running into optimizer bugs. The video should have started with a caveat about such possibilities.

Exactly. Just write idiomatic SQL and let the internal execution plan take care of the optimizations. No need to be super clever when it comes to SQL. If the query is "par for the course" the engine is coded to optimize your stuff.

Please don’t break up queries into separate pieces if at all possible. Currently working on 2 performance issues where people thought pulling back the records and doing in memory checking was fine. Worked for a few years till it mysteriously started randomly failing no one knew why and of course they could never get it to fail in development. I tracked it down and figured out that in production the query brings back 40k records in one case, turns out it only needed one of them.

@@MattiasFlodinis it really? If you are joining smaller table and condition join on same parameters you would filter subquery - what is the difference really? My totally anecdotal evidence based on working with Oracle db tells me different story - subqueries if anything, usually are slower .

👀

The video is a bit clickbait, it gets more honest in the end. The pattern presented isn't necessarily better, it really depends on the data at hand. Unless the query actually computes and throws away a lot of data the join version would be more optimizer-friendly and thus better in general.

Yeah at the very end he hits on the main reason to use a subquery over a join. You want to filter on data from another table but don't need it in your final result set. (In this video that would be a subset of users based on the posts data, but we don't care at all about any columns in posts for our output from the query).

The optimizations that apply to IN and EXISTS aren't the same optimizations that apply to JOIN, mostly. The JOIN itself is doing something completely different from IN and EXISTS, and that "completely different" thing is why the "duplicate" records exist. So, one of the things that's adding overhead to the JOIN over the the IN and EXISTS queries is the distinct keyword. It's telling MySQL it has to go through the result set, compare the records, and only return a single copy of any record that's repeated multiple times. The IN and EXISTS queries don't have to do this at all.

I think the message here is to be open to breaking free from the default-approach. It's always good to experiment with and benchmark more than one one query before deciding what to commit to the production code. I usually do that whenever the query time is a concern and in some case the query that proved to be better was the least expected.

Not just MySQL, other engines like MSSQL also differs in IN and EXISTS, IN can't deal with NULLs, always use EXISTS when filtering from another table to avoid confusion.

@@_MB_93 SQL is declarative, so it should produce the same execution plan because there is literally no case where this should produce different results given that IDs can't be null (and RDBMS knows it). Although, if column under discussion is nullable then it can produce different query plan and it's totally understandable.

In my experience they're quite often different in PostgreSQL, for good or ill. Usually EXISTS is the correct choice. I have seen one case in my career so far where IN happened to not just mean the right thing but perform better. The query was a huge complex mess with more than one bit throwing the query planner for a loop, so I'm just going to call it a curious exception to the rule.

Bro, I replaced once IN with Exists in a 2-hour-long query and reduced it to 6 seconds. His explanation explains why Exists sometimes can be faster than IN. However, if you have a long FIXED LIST returned, IN can perform better than Exists, due to the fact it's going to create the list ONCE and reuse the same list. Exists, on the other hand, will always run line by line when connected with the ID of one or more tables from the outside.

​@@prooseenonsensical statement. Barring bugs changing execution plan does not change result and any single statement can have 1 to near infinite amount of query plans.

I wasn't worried about this, but now I'm curious. This is why I love these videos. Even if I knew something already, the comments also provide interesting insights.

I think in MySQL `where in` has a limit of 10k items

@Raftor74 I wonder if that's also true for sub-queries. I've seen the same issue when doing it in a two step approach - pulling the IDs in first, and then constructing an IN query - But I'm not sure about actual "IN sub-query" queries.. @PlanetScale ?

For default postgres, yes it is.

yes but you can bypass it by encasulating your where in in a where and add or where in

@jonasjonaitis8571 yeah both are worth considering.

@jonasjonaitis8571 This will definitely negatively impact performance since you are going over the wire multiple times. Keep in mind that under the hood, a database query issued from your applications still just sends a tcp request and all of the encoding, packeting, etc that is involved in that.

In real-world apps (eg telco/location data with billions of rows per day) subqueries fall apart pretty quick. If you're working on toy datasets (

@@boccobadz totally agree. And given that each method returns the required data in less than 1 ms, optimization at this point is a waste of time. I mean, sure you can say that the left join method was 2x slower than the others, but 0.3 ms vs 0.7 ms is a very negligible difference.

@jonasjonaitis8571 Subqueries are pretty much always going to be faster than running separate queries, since in the worst case the subquery approach will do the exact same thing except not round-trip the useless intermediate results over the network and serialization logic.

I came along with the same situation with invoices and customers (both tables have >10k rows). The speed of subselects are better and the data overhead is much smaller. Like he sad in the end of the video.. if you just need data from table A related on table B... do a subselect.

Totally agree. If your tables only have 100 records in them then SQL is not even going to use the indexes anyway so it largely doesn't matter how you query the data because it's always going to use a full table scan and be "fast enough".

@@Tjommel for me large table is something that dont fit into single worker memory

Ofc if you work with time critical applications than you will try to squezee every milisecond of each stage of your app.

I just did a quick and dirty test on a table with 100,000 rows matching against a subselect of 1.5mil rows and the join is ~1.15seconds on average and the exists is... 3.4 seconds on average. A group by instead of distinct gives me the same 1.15seconds. I think the moral of the story is don't listen to blanket advice from someone selling you services billed by the query, profile your code and find out which is faster for you and if you've got time see if it's faster because of a mistake that's fixable.

Yes, I was thinking 1 & 2 as well. Inner join and moving the conditional to the join would probably be a lot more performant. I'm not sure what you mean in 3. Do you mean to run them as separate queries? He talked about that in the video.

I do agree, a inner join in this case seems more appropriate

Yeah inner join would've been more appropriate for the real case, but this sample data wouldn't have changed. Just an oversight!

You have to watch out with Distinct, it can eat up your memory on huge tables since it does everything in memory. For extremely large queries, I would just write a proc so that you can take advantage of temp tables to improve the performance especially on large table joins that are used multiple times. However, each database engine will have different performance enhancements that might make one approach more effective on one database, but not on another.

been there. personally i always found subqueries to be a foot gun in most of situations that i ran into.

The IN clause has size limits when you're binding in params, not with a subquery. With a subquery it's a semi-join or an anti-join.

@@PlanetScale good to know, I was actually wondering about it and about to look it up, but now my question has been answered. 😄

There’s no reason to avoid using WHERE EXISTS. That’s literally its sole purpose; to perform an existence check. It’s preferable to joins in this instance because DISTINCTs can be costly. And similar to joins, WHERE EXISTS can handle comparisons that involve “joining on” multiple columns, unlike IN which is just comparing 1 column vs 1 column. Additionally, NOT IN can result in some “gotchas” if one of the values can possibly be NULL. So really there is no reason not to use WHERE EXISTS / WHERE NOT EXISTS; it performs as well if not better than joins or IN/NOT IN, and has less caveats.

Yeah, I'm a bit confused by that as well. In general, an inner join should perform the same, if not better, than a left join. As well, we're looking for users that *do* have posts, so it makes more sense to use an inner join since that'll ensure only users with posts are actually pulled back. At my previous job, I managed to improve performance of a lot of queries by moving from outer joins to inner joins when they made more sense, and I feel like using an inner join in this example might bring the performance of the join query closer to that of the sub queries. Then, I think about the only real performance hit would be from the "distinct" operation.

Yeah that first example would've been better with an inner join, but with this data it wouldn't have changed

It can lead to indices to not be used efficiently when you're pulling out a large portion of the table.

​@@magfalSounds like you need to add indexes, if the columns being queried is not indexed it would lead to that. The views column in the video is most likely indexed.

@@magfal If you only have one matching row in each table then a join can be better, the same of cause if you need the data from both tables and use group by and aggregate data or want multiple lines. But if the table used in the subquery will not directly contribute to the returned data but only be part of the condition, then a subquery can often be better since it can work with the tables separately and then match them on the result which in many cases is a much smaller set of rows, especially if you have limiting conditions on both tables. Modern SQL often can optimize this under the hood even with joins, have not worked with mysql and that kind of data volumes and even when I did it was over a decade ago so I do not know how good mysql it (probably leages better than back then ;) ) but I know MSSQL server can rewrite the execution plan quite a lot. I also know that at least mssql (and I assume mysql) can improve query plans that are used multiple times since once it has run once or twice it can use the actual return data to judge if the created plan was the best or if the data pattern might allow for even more optimizations.

@@daleryanaldover6545 A subquery used like this can result in a full table scan if a threshold of expected rows is met. I'm not saying not to use the technique but be aware that your results may vary based on the ratio and shape of your data and indices. You should A-B test subqueries, materialized CTEs and joining full table, they all have benefits and drawbacks in different situations and when using different SQL engines. Postgres is less prone to give you this problem but I've experienced it with both MSSQL and MySQL.

consider all options and then test them. you don't want to come back to this after 2 years and have to reverse engineer it in order to fix a performance issue. just make sure its good from the start.

I'm interested to know this too

While what you're asking does make sense (if table A has more records than table B, then it must take less time to execute something against table A than B), it doesn't necessarily work that way in a database. In a database, you're usually performing filters against indexed fields, which essentially makes the query only have to read a small subset of the entire table. Basically, to really see how it's going to perform in your case, you really need to either execute both queries and see which is faster, or run an explain statement to see what the query optimizer ends up doing with the query.

This depends on scale of the difference and also on the number of matching rows. For example, let's say you have just 10 "users" and a million of "posts", and an index on "posts" that lets us immediately find a post for a specific users with more than x "views". The "where exists" query just accesses the "posts" table by this index 10 times (once for each user) and completely ignores the remaining 999990 posts, whereas a join or a "and id in () ..." subquery probably result in accessing way more posts along the way. (Of course, there is always a chance that a super clever query optimizer in your database recognizes this case and switches to the faster access path). On the other hand, if you have 1000 users with a total of 1010 posts, and only 5 out of those posts have the required number of views, filtering posts first and (semi-)joining to users later is probably faster than accessing posts 1000 times for each user seperately.

The JOIN will not be just as fast because it has to compute additional rows in the results to accommodate the intersection with the right table. The IN/EXISTS has less work to do because it knows the results will only be some subset of a single set.

I wouldn't say it's that "MySQL is not very optimized". It makes sense because the queries, while producing the same result, aren't doing the same thing. For example, in the case of the join query, you have to perform an extra step, "distinct". "distinct" in every SQL database I've used is an expensive operation. The subquery method doesn't have to perform a "distinct" because it isn't returning back "duplicate" records. I'd imagine at least a decent bit of the join's overhead in this case is due to the distinct vs the join operation.

@@minilathemayhem Well the SQL engine should have used the information that the final results are distinct from the user table and skipped any joins on already matched users. Any well written SQL engine should have all the information to make predictive analyzis on how the joins and matches are performed. If the query was very complex then it's possible that distinct would be expensive but when it's very simply just the whole row of a table it should have been able to optimize the join and skip already matched results similiar to to the subquery is working.

@@KristjanB Every SQL implementation I've used (Postgres, SQL Server, MySQL) has had performance issues when performing distinct, so I don't know what to tell you beyond what I've experienced myself.

Wow this is new, I assumed the views column here is indexed. Also the ids would definitely be indexed.

You are oversimplyfing thing so much.

an issue he mentioned here is the duplication and that is why he went from join to subquery. if you use a join in this example, you will duplicate records that you will later discard.

not really

No in all of the cases here the database will join the tables. If it didn’t it would not be faster.

My rule of thumb is inner join > CTE (sql server)/subquery (non-sqlserver) > outer join

That's just a normal join.

good question

I am so thankful that PHP 8 added this. Makes writing raw queries so much cleaner and better. Old syntax was horrible.

Ah, thanks. I didn't know that.

Nice, nailed it

In the real world, you rarely need data from 1 table. That's the whole reason for using databases in the first place, otherwise using flat files with filters would be the way to go. Not only benchmark is done on a toy dataset, it's not even written in the way you would write it (left vs inner, condition placement, etc). Kinda clickbaity.

the time has come!

These are actually faster? I assume because we're not joining over data we'll throw away later anyway?

The real answer is "learn SQL, and learn how to check what the optimizer is doing."

To clarify your pattern for when to use join versus sub query. It boils down to determining if table B is a one-to-many and if that is what you want your results to look like. When you want to do a one-to-one then you sub query it. It comes down to knowing the relationship of your tables and what you need the end result to look like.

tableplus

I think a lot of people who use joins don't understand them. So many bugs I fixed with changing joins to subqueries.

I think it is best to be confused. Keeps it more exciting. Will the database burn? Iii dooont know! Lol 🤣

Yes. In my inexperience, I've always assumed that doing more network requests is worse. It's a heuristic that has served me well enough for now, but it would be cool to see how it might not be always true.

That's what the asterisk is for 😉

TablePlus

This is in MySQL, I don't know anything about SQL server!

Join and Subquery are not the same thing, they perform different functions. If they were the same thing we wouldn't be seeing all this advice to avoid subqueries - because they could just be translated to joins. They're different operations.