CROSS JOIN vs INNER JOIN in SQL Server 2008

Question

What is the difference between CROSS JOIN and INNER JOIN?

CROSS JOIN:

SELECT 

    Movies.CustomerID, Movies.Movie, Customers.Age, 

    Customers.Gender, Customers.[Education Level], 

    Customers.[Internet Connection], Customers.[Marital Status], 

FROM   

    Customers 

CROSS JOIN 

    Movies

INNER JOIN:

SELECT 

    Movies.CustomerID, Movies.Movie, Customers.Age, 

    Customers.Gender, Customers.[Education Level], 

    Customers.[Internet Connection], Customers.[Marital Status]

FROM   

    Customers 

INNER JOIN 

    Movies ON Customers.CustomerID = Movies.CustomerID

Which one is better and why would I use either one?

Answer 1

Following two examples will return the same result:

In Cross join,

select * from table1 cross join table2 where table1.id = table2.fk_id

In Inner join

select * from table1 join table2 on table1.id = table2.fk_id

The cross join will not combine the rows, if you have 100 rows in each table with 1 to 1 match, then you'll get 10.000 results, Inner join returns 100 rows in the same situation.

Note: It's recommended to use the second method.