Tuesday, October 29

Cross Join/ Cartesian


A Cross Join is that doesn't use a  WHERE clause will result in Cartesian product of the
tables involved in the join. The Cartesian result  is the number of rows in the first table
multiplied by the number of rows in the second table. If a WHERE clause is added, the cross
join behaves as an inner join.
Syntax:

SELECT column_name(s) FROM table1 Cross Join  table2;

Use AdventureWorksDW2008R2
select d.AccountKey,f.Financekey,d.AccountDescription, f.amount 
from DimAccount d CROSS JOIN FactFinance f --Cartesian Product Result

select d.AccountKey,f.Financekey,d.AccountDescription, f.amount 
from DimAccount d CROSS JOIN FactFinance f 
Where  d.AccountKey =f.AccountKey Order by AccountKey Asc --Inner Join Result

Output: