Use ANY/ALL to retrieve customer who placed the highest number of orders
Various ways to write. Your query, slightly rewritten to use JOIN and corrected to return all customers in case of ties:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in 
      ( select cust_id 
        from orders
        group by cust_id
        having count(*) &gt;=  
               all ( select count(*)
                     from orders
                     group by cust_id
                   )
      ) ;


Similar, without using &gt;= ALL:

select c.*, o.*
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in 
      ( select cust_id 
        from orders
        group by cust_id
        having count(*) =  
                  ( select top (1) count(*) as cnt
                     from orders
                     group by cust_id
                     order by cnt desc
                  )
      ) ;


The subquery simplified using TOP WITH TIES:

select c.*, o.* 
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
where c.cust_id in 
      ( select top (1) with ties m.cust_id 
        from orders as m
        group by m.cust_id
        order by count(*) desc
      ) ;


All of the above could be rewritten to use a derived table or CTE and JOIN as the subquery is uncorrelated.

Another way would be to use window functions in combination with TOP:

with 
  d as
  ( select c.*, o.*,
           cnt = count(*) over (partition by o.cust_id)  
    from customers as c
        join orders as o
          on o.cust_id = c.cust_id
  )
select top (1) with ties
    d.*
from d
order by d.cnt desc ; 


or just window functions:

with 
  d as
  ( select m.cust_id,
           cnt = count(*)  
           max_cnt = max(count(*)) over ()  
    from orders as m
    group by m.cust_id
  )
select c.*, o.* 
from customers as c
    join orders as o
      on o.cust_id = c.cust_id
    join d 
      on d.cust_id = c.cust_id
where d.cnt = d.max_cnt ;