Improving sub select query on two tables without relationship I am trying to return the count of employees by salary band and can return data using the following sql but wondered if this was the most efficient way of achieving it? 

I know creating a join between the tables would be better but that is not possible in this instance.

select *, (select count(*) from EmployeeSalary where salary between minsalary and maxsalary) from SalaryBands
It's odd you say it is not possible, because it is possible, right?

select ID, Name, MinSalary, MaxSalary, count(Salary) as SalaryCount
from SalaryBands
left outer join EmployeeSalary on salary between minsalary and maxsalary
group by ID, Name, MinSalary, MaxSalary


Now, I am not sure I would actually use that... I would actually go with your query, but transformed to be more readable...

select ID,
       Name,
       MinSalary,
       MaxSalary,
       (select count(*)
        from EmployeeSalary
        where salary between minsalary and maxsalary
       ) as BandCount
from SalaryBands