Home Library Learn Downloads Support Community
Sign in | United States - English | |
5 out of 13 rated this helpful - Rate this topic

Using Cross Joins

SQL Server 2008 R2

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.

Copy 
USE AdventureWorks2008R2;
GO
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BusinessEntityID;

The result set contains 170 rows (SalesPerson has 17 rows and SalesTerritory has 10; 17 multiplied by 10 equals 170).

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.

Copy 
USE AdventureWorks2008R2;
GO
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID;

-- Or

USE AdventureWorks2008R2;
GO
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
INNER JOIN Sales.SalesTerritory t
ON p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID;

Reference

WHERE (Transact-SQL)
SELECT (Transact-SQL)
Operators (Transact-SQL)
SELECT Examples (Transact-SQL)

Concepts

Using Operators in Expressions
Joining Three or More Tables
Using Inner Joins
Using Outer Joins
Using Self-Joins
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Good example for the same.

Q) With the following tables structures, show the Balance for each customer after deducting all the transaction amounts after the last balance date(CashLedger.LastDateBal) for that customer.
 
create table dbo.CashLedger
( CustomerId int
, Name varchar(100)
, Balance money
, LastDateBal datetime -- Last datetime when the balance was re-orged according to dbo.CashPayments
)
create table dbo.CashPayments
( TransactionId int
, DateOfTran datetime
, CustomerId int
, Amt money
, Operation varchar(100)
)
go
 
insert into dbo.CashLedger
select 101, 'Sam', 25000.00, dateadd(dd, -1, getdate())
union
select 102, 'Ram', 55000.00, dateadd(dd, -1, getdate())
union
select 103, 'Sif', 15000.00, dateadd(dd, -1, getdate())
union
select 104, 'Jack', 115000.00, dateadd(dd, -1, getdate())
go
delete from dbo.CashPayments
insert into dbo.CashPayments
select 1001, getdate(), 102, 100, 'New Transaction'
union
select 1002, getdate(), 101, 100, 'New Transaction'
union
select 1003, getdate(), 103, 75, 'New Transaction'
union
select 1004, getdate(), 102, 50, 'New Transaction'
union
select 1005, getdate(), 102, 400, 'New Transaction'
union
select 1006, getdate(), 101, 500, 'New Transaction'
union
select 1007, getdate(), 101, 150, 'New Transaction'
union
select 1008, getdate(), 101, 400, 'New Transaction'
union
select 1010, getdate(), 102, 300, 'New Transaction'
union
select 1011, getdate(), 103, 500, 'New Transaction'
union
select 1012, getdate(), 103, 200, 'New Transaction'
union
select 1013, '01/01/2010', 101, 2000, 'Old Transaction'
 
go
select * from dbo.CashLedger
select * from dbo.CashPayments
go

with cte
as(
select c.CustomerId, sum(p.Amt) as 'TotalAmt'
from dbo.CashLedger c
cross join dbo.CashPayments p
where c.CustomerId = p.CustomerId
and p.DateOfTran >= c.LastDateBal
group by c.CustomerId
)
select c.CustomerId, Balance, TotalAmt as 'Total Tran Amt', Balance-TotalAmt as 'Balance After Deduction'
from dbo.CashLedger c
left outer join cte on (c.CustomerId = cte.CustomerId)

History
When a WHERE turns a Cross Join into an Inner Join
A WHERE clause only turns a Cross Join into an Inner Join when it provides the join criteria, as in:
WHERE TableA.Key = TableB.Key

However, other WHERE criteria may be simple filters and will still produce a Cartesian product of all unfiltered rows.  For example:
WHERE TableB.Name LIKE 'A%'
History