CROSS JOIN vs INNER JOIN in SQL Server 2008

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.

Syntax for Inner Join

SELECT t1.column_name,t2.column_name
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name=t2.column_name

Cross Join

A cross join that produces Cartesian product of the tables that involved in the join. The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.

Syntax for Cross Join

SELECT * FROM table_name1
CROSS JOIN table_name2

Or we can write it in another way also

SELECT * FROM table_name1,table_name2

Now check the query below for Cross join

Example

SELECT * FROM UserDetails
CROSS JOIN OrderDetails

Or

SELECT * FROM UserDetails, OrderDetails