Have I created the sql query correctly?

Question

SQL

There are such tables:
Users table – site user:

• id - unique record identifier
  
• name - username
  
• age - user's age
  
• admin - true = site administrator, false = normal user
  

Article table - articles:

• id - unique record identifier
  
• user_id - user ID from the users table who wrote the article
  
• annotation - annotation to the article
  
• text - full text of the article
  

Table comments - comments to the article:

• id - unique record identifier
  
• user_id - ID of the user from the users table who wrote the comment
  
• text - comment text
  
• is_deleted - true = comment is marked as deleted, false = comment is not marked
  
• how remote
  

Table article_comment_association - linking comments to articles:

• article_id - unique article ID from the articles table
  
• comment_id - unique comment ID from the comments table
  

You need to find all usernames that have articles with comments.
Is it correct to make such a request?

SELECT users.name from users
INNER JOIN articles ON (articles.user_id = users.id)
INNER JOIN comments ON (comments.user_id = users.id)
GROUP BY users.name;

Answer 1

No, the request was made incorrectly! He, in addition to what is required, will also select users who wrote a comment, even if no one commented on their own articles ... not to mention that if there is no explicit need to aggregate fields in a group, then using GROUP BY there , where you can get by with DISTINCT - an expensive pleasure (why - I'll explain further, but for now, just for comparison, its plan and "cost"):
To just get the desired result, of course, you can stupidly supplement it with another JOIN with article_comment_association , but this still very bad: firstly, JOIN with a table of comments is simply superfluous there, and secondly, GROUP BY is still the same waste of resources:
Here, for comparison, the cost of DISTINCT vs. GROUP BY:
(I will not give all these incorrect options, so that they are not accidentally copied into the nuclear reactor control system!)
In this sense, the option proposed by Rsa97 is already better, because gives the correct result.

SELECT name FROM users
  WHERE id IN (
    SELECT user_id FROM article
      WHERE id IN (
        SELECT article_id FROM article_comment_association
      )
  );

However, using subquery in this order does not really allow distinct:
The trick is that subquery tends to create a temporary table, usually in memory, but if there is not enough of it, then on disk. So, if it is possible to replace them with JOIN (and it is almost always there!), This should be done without hesitation.
And here is the Feng Shui (it is also the correct, easy to read, obvious and effective) version of the request:

SELECT distinct users.name from users
  INNER JOIN article ON (article.user_id = users.id)
  INNER JOIN article_comment_association ON (article.id = article_comment_association.article_id)

...and her plan:
Moral of the story: In a relational database, the most direct route to a desired result is usually the most efficient. As a general rule, you should start with the largest possible set of records, eliminating as many unnecessary ones as possible at a time, and letting the optimizer use the indexes.

Answer 2

SELECT `name`
  FROM `users`
  WHERE `id` IN (
    SELECT `user_id`
      FROM `article`
      WHERE `id` IN (
        SELECT `article_id`
          FROM `article_comment_association`
      )
  )

Answer 3

Fluently - it seems like the right option.
By the way, you can also approach from the other side:
comments - articles - users