Search code examples
postgresqlinner-joincross-join

What's the difference between a cross join and an inner join with identical filter?

Let's see a cross join:

select c1, c2, c3
from t1
cross join t2
where t1.f1 = t2.f2

and let's see an inner join:

select c1, c2, c3
from t1
inner join t2
on t1.f1 = t2.f2

What is the difference between the two statements in terms of performance, functionality and memory usage?

Solution

  • These two queries are functionally identical, as is the following query:

    select c1, c2, c3
from t1, t2
where t1.f1 = t2.f2

    What follows is my personal opinion:

    Always write inner joins with the JOIN ... ON ... or JOIN ... USING (...) syntax. The advantages are:

    1. It is immediately clear to the reader what you are doing and what the join condition is.

    2. You can never forget to write a join condition, because you are required to write one.

      This protects you from queries that return 1 billion rows instead of 10000 just because you forgot some join conditions, which is a frequent beginner's mistake.

    Also note that while for inner joins it doesn't matter if you write a condition in the JOIN or in the WHERE clause, it matters for outer joins.