Skip to main content

Posts

Showing posts with the label CROSS JOIN vs INNER JOIN in SQL Server

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 testt