Using Cross Joins

We can use Cross Join with WHERE clause and Without WHERE clause, When we use the Cross Join without WHERE clause,produces the Cartesian results from the tables. The resultset contains records that are multiplication of record number from both the tables.

see the Following example:

Suppose we have two tables:
tbl_Employee

Employee Id EmpName Age Salary Department
1 Ankur 28 1200 Sales
2 David 32 3400 Sales
3 John 30 1500 HR
4 James 45 1200 Production
5 Mohan 35 4500 Production
6 Ram 40 2300 HR
7 McDonald 30 2600 HR
8 Rohan 23 1600 Salse

 
tbl_EmployeeDetails

Employee Id Adres
1 Noida
5 Delhi

 
See the query:

SELECT     dbo.tbl_Employee.EmployeeId, dbo.tbl_Employee.EmpName,
 dbo.tbl_EmployeeDetails.Addres
FROM         dbo.tbl_Employee CROSS JOIN
                      dbo.tbl_EmployeeDetails

 
The result set contains 16 rows (tbl_employee has 8 rows and tbl_EmployeeDetails has 2; 8 multiplied by 2 equals 16).
See the following picture:
 
Cross Join without WHERE clause between sql tables
 

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 tbl_Employee.EmployeeId, tbl_Employee.EmpName, 
tbl_EmployeeDetails.Addres
FROM  tbl_Employee INNER JOIN tbl_EmployeeDetails 
ON tbl_Employee.EmployeeId = tbl_EmployeeDetails.EmployeeId

— Or

SELECT  tbl_Employee.EmployeeId, tbl_Employee.EmpName,
 tbl_EmployeeDetails.Addres
FROM  tbl_Employee CROSS JOIN tbl_EmployeeDetails 
WHERE tbl_Employee.EmployeeId = tbl_EmployeeDetails.EmployeeId

Result would be:

Employee Id EmpName Adres
1 Ankur Noida
5 David Delhi