Performance of inner join compared to cross join

Question

The effect of issuing an inner join is the same as stating a cross join with the join condition in the WHERE-clause. I noticed that many people in my company use cross joins, where I would use inner joins. I didn't notice any significant performance gain after changing some of these queries and was wondering if it was just a coincidence or if the DBMS optimizes such issues transparently (MySql in our case). And here a concrete example for discussion:

SELECT User.* FROM User, Address WHERE User.addressId = Address.id;  SELECT User.* FROM User INNER JOIN Address ON (User.addressId = Address.id); 

Answer 1

Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 6 rows and table B has 3 rows, a cross join will result in 18 rows. There is no relationship established between the two tables – you literally just produce every possible combination.

With an inner join, column values from one row of a table are combined with column values from another row of another (or the same) table to form a single row of data.

If a WHERE clause is added to a cross join, it behaves as an inner join as the WHERE imposes a limiting factor.

As long as your queries abide by common sense and vendor specific performance guidelines (i), I like to think of the decision on which type of join to use to be a simple matter of taste.

(i) Vendor Specific Performance Guidelines

1. MySQL Performance Tuning and Optimization Resources
2. PostgreSQL Performance Optimization

Answer 2

There is no difference other than the inner join is a lot clearer because it defines the join, leaving the where clause to be the actual limiting condition.