Tag Archives: cross join sample

Cross Join to Generate Every Combination

Cross Joins are useful in Business Intelligence where you want to perform a cartesian product on two sets of data.  Possible uses are discussed below http://stackoverflow.com/questions/219716/what-are-the-uses-for-cross-join … in fact, it even makes a good business name http://www.crossjoin.co.uk/ … 🙂

Here is a basic example of how a cross join works using userid’s and dates.


CREATE SCHEMA Demo
GO
CREATE TABLE Demo.Users

( UserId varchar(10)

)
GO
INSERT Demo.Users
VALUES
('User1')
INSERT Demo.Users
VALUES
('User2')
INSERT Demo.Users
VALUES
('User3')
CREATE TABLE Demo.Dates

( DateValue date

)
GO
INSERT Demo.Dates
VALUES
(getdate())
INSERT Demo.Dates
VALUES
(getdate()-1)
INSERT Demo.Dates
VALUES
(getdate()-2)

SELECT Users.UserId, Dates.DateValue
FROM Demo.Users
CROSS JOIN
Demo.Dates