But the example is exactly the type of query where I often see DISTINCT used where it shouldn't: they just want to know the customer with at least one order or the author who wrote at least one book or the user who upvoted at least one comment. I think that was the point of the post (not to never use DISTINCT or that this is the only example where anyone ever needs to join). DISTINCT isn't likely to be used in the more elaborate example you mention (total products from some category) because DISTINCT doesn't fix a problem that the necessary GROUP BY to get the answer has already prevented appearing anyway.
If you don’t need the result from the joined table, a semijoin is often the fastest way. In the given example, if there was more data in the tables, it’s likely the planner would choose that. Not as familiar with MSSQL as MySQL and Postgres so I’m not positive.
Also, the good news is the planners are increasingly better at converting your desired end state into queries utilizing features like this, without you explicitly telling them to.
