Using CROSS JOIN vs INNER JOIN in SQL Server all version same concept

Cross join

Each row from testtable1 will return with each row from testtable2 because there will be no join condition.

without joining condition we can return multiple data from tables using cross join,but cross join based on Cartesian product.it will return total rows,for example employee table has 14 rows,dept table 4 rows,14*4=56 rows it will return.

use to retrieve the data from more than one table with out any condition.no need to have any common column b/w to apply this join.because,20 employee and 4 dept so 20*4=80 it will return the 80 rows selected.

This kind of thing is called as Cartesian Product. If where clause is used with CROSS JOIN, it works like an INNER JOIN in query .

SELECT
    col1,col2,col3
FROM  
    testtable1
CROSS JOIN
    testtable2

Inner join

SELECT
    col1,col2,col3
FROM  
    testtable1
INNER JOIN
    testtable2 ON testtable1.id1= testtable2 .id1


These 2 examples will return the same result:

Cross join

select * from testtable1 cross join testtable2 where testtable1.id1 = testtable2 .id1
Inner join

select * from testtable1 join table2 on testtable1.id1 = testtable2 .id1