Guides › CROSS JOIN

CROSS JOIN in SQL Server: every combination

CROSS JOIN pairs every row on the left with every row on the right. No ON. Ten staff and seven days is 70 rows. Ten staff and ten thousand orders is a mess.

A roster grid

SELECT s.first_name, d.day_name
FROM Staff s
CROSS JOIN (VALUES ('Mon'),('Tue'),('Wed'),('Thu'),('Fri')) d(day_name)
ORDER BY s.first_name, d.day_name;

Forgot the ON

An old-style comma join FROM Orders, Customers is a CROSS JOIN. Then a WHERE that was meant to be the key. Miss the WHERE and the total is orders × customers. If a number looks ten times too big, count the rows first.

Try it on Harbour & Co

James Rowe (Sales Manager) needs this now: “Can you resend last week’s orders with the customer’s name instead of their ID? Newest first.” Write the query, run it, send the results. Free.

Take the ticket →

Learn it on a real ticket

Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.

Clock in free See Monday’s tickets