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

Question

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?

Answer 1

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.