How to make a query without subqueries?

Question

Oracle

Please help me make a request. There are no problems with using subqueries, but how to compose it without subqueries.?

There are 2 tables - groups (id_group, name_group), users (id_user, id_group > fk groups, user_name).

You need to select groups with the number of users from 5 to 10. The result is in the form of a table id_group, name_group, size_group (number of users).

Answer 1

I'm not familiar with the intricacies of Oracle, but you can try this ( SQL HAVING Clause ):

select 
   groups.id_group, 
   groups.name_group, 
   count(distinct users.id_user) size_group
from groups
join users on users.id_group = groups.id_group
group by groups.id_group, groups.name_group
having count(distinct users.id_user) between 1 and 5
;

SQL fiddle

Answer 2

In most cases, the query planner's intelligence is enough to ensure that the options with subqueries, cte or having are essentially identical in terms of execution. Therefore, the clarity / aesthetics of the request text itself will come to the fore sooner.
ps well, looking into the future: probably someday the criterion "from 5 to 10" may turn into "from N to M", plus "for only ... groups and only users with characteristics ..." -> the request will turn into stored procedure with a bunch of parameters and hinting nuances will arise there