SQL Joins and Execution Plans

What they ask

The technical interview for developer and data roles leans hard on SQL Server. Three themes come up: joins, reading an execution plan, and query optimisation. The HR part of the same session asks about previous experience and, for fresh graduates, how you worked with data during college. Questions in this area, for example:

Joins. Given customers(id, name, city) and orders(id, customer_id, total, created_at), write a query that returns every customer with the number of orders and total spend in 2025, including customers with zero orders.

Example: customer Mona with orders 200 and 300 in 2025 returns Mona, 2, 500; customer Adel with no orders returns Adel, 0, 0.

Then: rewrite it to return only customers who never ordered (anti-join), and explain the difference between LEFT JOIN ... WHERE right.id IS NULL and NOT EXISTS.

Execution plans. What is the difference between an index seek and an index scan? When does the optimiser choose nested loops vs hash join vs merge join? What does a key lookup tell you, and how would you remove it?

Optimisation. Why WHERE YEAR(created_at) = 2025 cannot use an index on created_at, and how to rewrite it. When a covering index is worth its write cost.

What they look for

Correct joins with the aggregation on the right side of the join, and the ability to read a plan and say what you would change, not just that "it needs an index".