CODEWITHSUNDEEP
mysqlsqlcross-join
Shafizadeh
Shafizadeh

Reputation: 10340

When is CROSS JOIN useful?

When I read the documentation about CROSS JOIN, I wondered, because it seems pointless. You can do that using just a comma ,. I tested it (several times) and the results of these queries were exactly the same:

// one
SELECT * FROM table1 CROSS JOIN table2

// two
SELECT * FROM table1, table2

Anyway, I want to know: is there any logical reason for using CROSS JOIN instead of ,?

Upvotes: 1

Views: 184

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Consider the following...

EXPLAIN EXTENDED
SELECT * FROM my_table, my_table x;

SHOW WARNINGS; (reformatted for clarity)

select test.my_table.id AS id
     , test.my_table.car_id AS car_id
     , test.my_table.car_model AS car_model
     , test.my_table.car_features AS car_features
     , test.x
     . id AS id
     , test.x.car_id AS car_id
     , test.x.car_model AS car_model
     , test.x.car_features AS car_features 
  from test.my_table 
  join test.my_table x

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Apart from the simple rule of never using commas in the from clause and always using explicit join syntax, there is a good reason. The issue is the difference between these two queries:

select *
from table1, table2;

and

select *
from table1 table2;

These do very different things, and it can be rather hard to spot the difference (particularly in a more complicated query). If you never have commas in the FROM clause, then your queries will be easier to read and less prone to typos and other problems.

Upvotes: 4

Related Questions