Query for selecting all customers with the total number of orders in a specific range [closed]

As the title says, I want to select all customers with the total number of orders in a specific range, I came up with the following SQL query :

/*get all customers with total number of orders between 3 and 6*/
select 
    customers.id, customers.first_name, customers.last_name, orders.id
from
    customers 
inner join 
    orders on orders.customer_id = customers.id
where 
    customers.id in (select orders.customer_id 
                     from orders
                     group by orders.customer_id 
                     having count(orders.customer_id) between 3 and 6);

I want to know if this is the proper way to do it.

Is there any better way to write this query? Do I need to use always sub-query for this task?

Answer

Try this, you can get rid of the where clause:

select c.id, c.first_name, c.last_name
from customers c
inner join orders o on o.customer_id = c.id
group by c.id, c.first_name, c.last_name
having count(*) between 3 and 6;

or another version, depending on your preference:

select c.id, max(c.first_name) first_name, max(c.last_name) last_name
from customers c
inner join orders o on o.customer_id = c.id
group by c.id
having count(*) between 3 and 6;

To also select order.ids:

select c.*, o.id
from orders o
inner join (
  select c.id, max(c.first_name) first_name, max(c.last_name) last_name
  from customers c
  inner join orders o on o.customer_id = c.id
  group by c.id
  having count(*) between 3 and 6;
) c on c.id = o.customer_id

or a different version:

select c.id, c.first_name, c.last_name, o.id
from orders o
inner join customer c on c.id = o.customer_id
inner join (
  select o.customer_id
  from orders o
  group by o.customer_id
  having count(*) between 3 and 6;
) fc on fc.customer_id = c.id

Using exists:

select c.id, c.first_name, c.last_name, o.id
from orders o
inner join customer c on c.id = o.customer_id
where exists (
  select *
  from orders oi
  where oi.customer_id = c.id
  group by oi.customer_id
  having count(*) between 3 and 6;
)