CROSS JOIN:

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join.

Example:

select        E.EmployeeID, E.EmployeeName, E.Email, E.DepartmentID, D.DepartmentName
from          Employee E
cross join    Department D

The result set contains 15 rows (Employee has 3 rows and Department has 5; 3 multiplied by 5 equals 15).

Note: 

However, if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set.

select        E.EmployeeID, E.EmployeeName, E.Email, E.DepartmentID, D.DepartmentName
from          Employee E
cross join    Department D
where         E.DepartmentID = D.DepartmentID

(or)

select        E.EmployeeID, E.EmployeeName, E.Email, E.DepartmentID, D.DepartmentName
from        Employee E
inner join    Department D
on            E.DepartmentID = D.DepartmentID