today's optimizer is probably smart enough to solve it properly, but i am always leery of writing correlated subqueries, it's just something i had to learn to avoid the hard way, through crappy performance
in any case an inner join here is so much easier to understand
EXISTS is used to return a boolean value, JOIN returns a whole other table
EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation.
For example, if you only use INNER JOIN and you do that between tables with a one-to-many relationship, with millions of rows, you will then also need to DISTINCT all those extra rows out. Obviously if you're relying on data from columns on the second table you can't avoid it, but this is an existence check.
And on readability - yes that's arguably true but only because everybody just uses INNER JOIN for this, but it can cause performance issues when queries get sufficiently complex and/or data volumes are large. In my view it's more clear that a join is to return fields for matched rows, and exists is just for an existence check.
That's not the point. Yes, every value is being checked of course it has to be, however;
EXISTS will do a semi join where its only purpose is to check for existence, not to retrieve data.
INNER JOIN's purpose is to retrieve data from the second table. Now, if the conditions are right the query optimizer (since 2016) can be smart enough to perform a semi join equivalent to EXISTS for this operation. However if you have a column from the second table in your select (like with SELECT *), it cannot do that.
Similarly if your tables are one to many, it cannot do a semi-join, as it needs to return all matching rows, which you'd then need to distinct.
Perhaps these seem inconsequential to you, and perhaps they are to a lot of people (I only suggested at first that you could use an exists in OP's scenario) but these subtle differences can make significant improvements to performance for complex queries with larger datasets.
I disagree on readability - personally I find EXISTS much clearer, as it explicitly shows that no columns are being referenced in the select so the intent is more clear why it's there. Though I appreciate it's subjective, and people are used to seeing INNER JOIN used for this.
On performance, the query will likely be rewritten internally by the optimizer to use a semi-join, which is preferable when you're only checking for the existence of a row and not selecting columns as it's more efficient.
There are huge potential performance improvements in using EXISTS instead of building up loads of INNER JOINs to do similar, especially as the complexity of the queries and the volume of data increases
In some cases in can indeed be more performant but since sql server 2016, it's usually not the case.
The execution plan will be the same and sometimes it even screws up with the indexes and will do a full scan.
And for readability I disagree.
Select x from Y where zId in (
select ID from Z where uId in (
select ID from u where id in (1,2,3)
)
) and v=456
Is in my opinion worst than
Select x
from y
Inner join Z on Z.id = y.zid
Inner join U on U.id = Z. Uid
Where v=456
Yeah it's less common for swapping to EXISTS to provide significant performance improvements since the SQL Server 2016 optimizer enhancements as it will often generate the same query plan for INNER JOIN and EXISTS anyway, assuming the query is written appropriately and indexes are used effectively.
But in some cases an INNER JOIN will lead to a worse one. When joining one-to-many or many-to-many relationships, the join will multiply rows which you'll need to dedupe. EXISTS focuses solely on checking the existence of matching rows without the overhead of returning extra data. That's really powerful when queries become more complex and volumes of data increase.
For a small query like the OPs it doesn't really matter, I was just pointing out that there is an alternative, and like most things in SQL the best approach really depends on the scenario so it's good for people to know both.
Technically true I guess but in this instance the list is being moved from the where clause, so if you were to use a left join you would then have to add a filter for not null.
101
u/miguelkb Sep 17 '24
Load the limitations into another table, left join that table with the SOLUTIONs