Implicit Joins vs. Explicit JOIN Syntax: Why the Comma Notation Stays Risky

1. What the implicit comma syntax technically means

With implicit join syntax, multiple tables are simply listed comma-separated in the FROM clause, and the actual join condition moves entirely into the WHERE clause. From the SQL standard's perspective, that's not a separate language construct, it's just a CROSS JOIN, the formation of every possible combination of rows from both tables, followed by filtering via WHERE that reduces those combinations down to the row pairs that actually belong together.

Syntactically, FROM a, b WHERE a.id = b.a_id is therefore exactly equivalent to FROM a CROSS JOIN b WHERE a.id = b.a_id. The optimizer in most modern database systems recognizes this pattern and internally converts it into a regular INNER JOIN, so the two notations don't differ in execution speed in practice once the WHERE condition is written correctly.

The fundamental difference isn't the performance of a correct query, it's the class of mistakes each notation allows. Comma syntax doesn't syntactically tie the join condition to the tables involved, it's just one of potentially many WHERE conditions, which makes it easy to overlook or accidentally omit.

-- Implicit syntax: join condition lives inside the WHERE clause
SELECT o.id, c.name
FROM orders o, customers c
WHERE o.customer_id = c.id
  AND o.status = 'shipped';

-- Explicit syntax: join condition is syntactically bound to the join
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped';

2. How a forgotten WHERE condition turns into a cartesian product

The practically most dangerous case shows up when the join condition is simply missing from comma syntax, whether from a typo, an accidentally deleted AND, or because a necessary condition gets forgotten when a third table is added later. The result isn't a syntax error, it's a cartesian product: every row of the first table gets combined with every row of the second table.

With two tables of a thousand rows each, that produces a million result rows, the query keeps running, appears to return plausible data, and in many cases produces no obvious error, especially if the application only displays or processes the first few rows of the result. That very inconspicuousness is what makes the bug so dangerous: it often goes unnoticed during development with small test data volumes and only surfaces in production with real data volumes, where it causes drastically increased load, intermittent timeouts, or obviously wrong report numbers.

With explicit JOIN syntax, this failure mode is structurally harder to trigger. A JOIN without an ON clause is either a syntax error that immediately rejects the query, or requires the explicit and therefore deliberate use of CROSS JOIN, which is unlikely to happen by accident because it's a different keyword than the familiar JOIN and therefore stands out visually.

-- Dangerous: if the second line is missing, a cartesian product results
SELECT o.id, c.name, i.product_name
FROM orders o, customers c, order_items i
WHERE o.customer_id = c.id;
-- Missing condition: AND i.order_id = o.id
-- Result: every order combined with every item of every other order

-- Explicit JOIN forces an ON clause per table,
-- a syntax error surfaces the problem immediately while writing
SELECT o.id, c.name, i.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id;

3. Readability advantage of explicit JOIN syntax with many tables

For a query across two tables, the readability difference between the two notations is small. But once five, six, or more tables are involved, as is common in complex reporting queries, a clear difference emerges: with comma syntax, every join condition sits indistinguishably among the business filter conditions in a single, often long WHERE clause. A reader has to analyze each individual condition to figure out whether it expresses a join relationship or a plain data filter.

With explicit JOIN syntax, every join condition sits directly next to the table it belongs to, in the order the tables actually get connected. That spatial proximity between the JOIN keyword, the table name, and the ON condition makes the query's structure recognizable at a glance, without having to mentally split the entire WHERE clause into join and filter conditions.

Another readability advantage shows up with outer joins. LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN can't be expressed with pure comma syntax at all, or only through database-specific, non-standard extensions, which further hurts both portability and readability. Explicit JOIN syntax, by contrast, covers every join type uniformly and in a standards-compliant way.

4. Historical context: why the comma syntax still shows up at all

Explicit JOIN syntax with the JOIN keyword and the ON clause wasn't introduced until the SQL-92 standard. Before that standard, comma syntax was the only way to combine multiple tables in a query, and many database systems and textbooks from that era established it accordingly as the standard pattern. Anyone who learned SQL in the nineties or earlier often encountered this notation as their first and only form of join.

That historical imprint still lingers today, both in education and in existing code. Many legacy codebases that grew over decades still contain comma joins from a time when they were simply the only syntax available, and those queries keep running unchanged because they work and a refactor without a concrete trigger rarely gets prioritized.

Another reason for its persistence is that comma syntax actually works fine for simple two-table queries and looks shorter at first glance, so it keeps showing up in tutorials, Stack Overflow answers, and quickly assembled queries, even though the SQL-92 standard is now more than three decades old and explicit JOIN is fully supported by practically every modern database system.

5. Are there actual performance differences between the two notations?

With a correctly formulated WHERE condition, most modern database systems produce the same or a very similar execution plan for both notations, because the optimizer recognizes the join condition regardless of whether it syntactically sits in an ON clause or a WHERE clause. An EXPLAIN comparison between a cleanly written comma query and the equivalent explicit JOIN query typically shows identical plans.

A relevant difference does arise, though, in how an optimizer orders multiple join conditions, especially for very complex queries with many tables and mixed inner and outer join requirements. Explicit JOIN syntax lets you precisely control join order and join type per table pair, which is practically impossible to express cleanly for outer joins with comma syntax and can therefore indirectly lead to suboptimal plans when developers fall back on database-specific workarounds.

So the real performance difference between the two notations almost never shows up in correct code, it shows up indirectly through the higher error probability of comma syntax: an accidental cartesian product is always dramatically slower than any correctly planned join, regardless of the notation chosen.

6. Why mixed notation within a single query should be avoided

Some queries combine explicit JOIN for part of the tables with comma syntax for the rest. That's syntactically valid, but it significantly raises the cognitive load of reading the query, because a reader has to apply two different mental models simultaneously to reconstruct the full join structure. Especially in combination with outer joins, this mixture can lead to unexpected results, because the order in which conditions get evaluated depends on which notation is used.

A team style guide should therefore not only mandate explicit JOIN syntax, but explicitly prohibit mixed notation within the same query too. That rule is easier to enforce than it might first appear, because modern SQL formatters and linters reliably detect both patterns and can flag them automatically.

When migrating an existing comma query to explicit JOIN, it's also worth completing the switch in a single step rather than converting table by table incrementally. A half-migrated query with mixed notation is harder to review during the transition period than either the original or the fully migrated version.

7. Automated detection of comma joins in code review

Most SQL linters detect comma syntax as a distinct, checkable pattern: multiple table references in the FROM clause without a JOIN keyword. Like other SQL antipattern checks, this can be wired into the CI pipeline as a mandatory step, so a merge request introducing new comma syntax gets rejected automatically before a human even has to review the query manually.

For existing code that still contains comma joins throughout, a full migration in a single step is rarely realistic. It's more practical to enable the linter for new and changed code only at first, so no retroactive check of the entire codebase gets forced, while every new query automatically falls under the stricter rule.

Beyond the automated check, it's worth running a targeted manual audit of the existing codebase for queries that combine more than two tables via comma syntax, since that's where the risk of a missing condition is highest. Those queries should be migrated with priority, regardless of how long the full conversion of the rest takes.

-- Linter pattern: multiple FROM references without a JOIN keyword
-- get flagged as implicit join syntax
SELECT * FROM a, b, c WHERE a.id = b.a_id AND b.id = c.b_id;

8. When a cartesian product is actually intended

There are legitimate use cases where a cartesian product is genuinely the desired result, say generating every combination from a list of sizes and a list of colors for a product variant matrix, or building a calendar series by crossing a number sequence with a start date. In these cases, the explicit CROSS JOIN keyword should be used, never comma syntax without a WHERE condition.

The difference is purely communicative, but decisive for maintainability: CROSS JOIN makes the developer's intent unambiguously explicit, while comma syntax without a discernible WHERE condition looks indistinguishable, to the next reader, from a forgotten join filter. A code reviewer who encounters CROSS JOIN immediately knows the cartesian product is intentional and doesn't have to trace through application code to figure out whether a condition is missing.

That explicit marking pays off especially well for automated linting rules: a rule flagging every comma-separated FROM clause as an error produces no false positives for genuinely intended cartesian products, because those take a syntactically distinct path through CROSS JOIN.

-- Deliberately intended cartesian product: build a variant matrix
SELECT s.size_label, col.color_name
FROM sizes s
CROSS JOIN colors col;

9. A practical migration strategy for existing comma joins

The first step of a migration is an automated inventory: a script that scans the entire SQL codebase for the pattern of multiple comma-separated table references in the FROM clause and sorts the results by the number of tables involved. Queries with three or more tables deserve top priority, because that's where the probability of a missing or faulty condition is highest.

For every migrated query, a comparison of the returned row count before and after the switch, and where possible a diff of the actual result data, should follow. If the results differ, that almost always indicates the original comma query already contained a latent, previously undetected bug, such as an incomplete condition that the migration now uncovers.

Once the migration is complete, it's worth locking in a permanent style guide rule and a corresponding linter configuration that blocks new comma joins entirely. That prevents the painstakingly cleaned-up codebase from gradually accumulating the same risk again through new contributions.

10. Summary

11. FAQ: Implicit vs. Explicit Joins: Key Takeaways