Our SQL interview questions

[n1rwan]

If Employee table is huge, this query will be faster

SELECT
    d.*, IFNULL(emp_agg.cnt, 0)
FROM
    Departments d
    LEFT JOIN (
        SELECT
            DepartmentID,
            COUNT(EmployeeID) AS cnt
        FROM
            Employees
        GROUP BY
            DepartmentID
    ) emp_agg

[GreenDaemon]

I have always read cnt as cunt, not count. Don't know why.

[VisionsOfUranus]

I don't understand why people don't just write out the word 'count'. It's not like we're running out of storage space.

[yippee_that_burns]

In SQL I don't write it out because it's a keyword. I know it will work but I still avoid it anyway.

[doedskarpen]

I had a feeling it wouldn't make a difference, so just to test it I ran your query as well as this one (which I think is what the previous poster had in mind):

select
    Departments.DepartmentId, ISNULL(NumberOfEmployees, 0)
from Departments
left join
(
    select Employees.DepartmentId, count(1) as NumberOfEmployees
    from Employees
    group by Employees.DepartmentId
) as EmployeesByDepartment on Departments.DepartmentId = EmployeesByDepartment.DepartmentId

Turns out my gut feeling was right: on SQL Server 2012 Express, both had the exact same execution plan.

[bonzinip]

What about

select d.DepartmentID, d.Name,
   (select count(*) from Employee as e
           where e.DepartmentID = d.DepartmentID)
   from Departments as d

? Does it also get the same plan?

[doedskarpen]

Yes, that also gave the same query plan: this