2022-05-03

Why I prefer INNER JOIN rather than Comma Separated Join

This month blog post I would like to discuss a topic about SQL coding style. Old-school SQL programmers mostly write table join query using comma separated style, e.g.
SELECT ... FROM TableA, TableB WHERE TableA.x=TableB.y
Yes, there are no any differences in terms of query result, execution plan, and performance. But INNER JOIN is considered better for readability, while comma-separated join can easily leads to human coding mistake.

Let's take a look at below example on StackOverflow2010 database:
-- INNER JOIN
SELECT COUNT(*) FROM Users U JOIN Posts P ON U.Id=P.OwnerUserId
WHERE U.DisplayName='Brent Ozar';
-- Versus Comma-Separated Join
SELECT COUNT(*) FROM Users U, Posts P
WHERE U.Id=P.OwnerUserId AND U.DisplayName='Brent Ozar';

Both of them are written correctly. So let's check their results, STATISTICS IO, and execution plans:



As you can see, they have no any differences. So some old-school folks prefers comma-separated, as they thought shorter coding means easier to read and write.

But how about if the programmer forgot to put in the table join predicates? Especially when a query involves multiple tables to join and complex filtering, it's comma-separated join is more error-prone than INNER JOIN. E.g.
-- INNER JOIN forgot to put in the ON clause
SELECT COUNT(*) FROM Users U JOIN Posts P
WHERE U.DisplayName='Brent Ozar';
-- Versus Comma-Separated Join forgot the table join predicate
SELECT COUNT(*) FROM Users U, Posts P
WHERE U.DisplayName='Brent Ozar';

In this case, INNER JOIN can prevent such coding mistake by throwing complication error. But comma-separated join will produce Cartesian product which gives you wrong result, table scan operators impose huge loading on your database server, and whole table being locked.

Take away:
Always use INNER JOIN (or JOIN if you like shorter coding) rather than comma-separated join!

No comments:

Post a Comment