Search code examples
mysqlsqljoincross-join

In SQL, what's the difference between JOIN and CROSS JOIN?

What's the difference between:

select t1.a1, t1.a2, t1.a3 from t1 cross join t2 where t1.a3 = t2.a1

and:

select t1.a1, t1.a2, t1.a3 from t1,t2 where t1.a3=t2.a1;

Can I use them interchangeably?

Solution

  • MySQL doesn't offer a distinction between JOIN and CROSS JOIN. They are the same.

    In both your examples the clause

    WHERE t1.a3 = t2.a1

    converts any sort of join into an inner join. The standard way of expressing this query is

    SELECT t1.a1, t1.a2, t1.a3 
  FROM t1 
  JOIN t2 ON t1.a3 = t2.a1