Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
544 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
A cross join is almost completely pointless! In 10 years of database development I've used one ... once (I'll reveal when and how at the bottom of this page).
A cross join isn't actually a join at all - it's what happens when you don't join tables together. There are two ways to write a cross join. Either explicitly:
FROM
tblAuthor AS a CROSS JOIN
tblBook AS b
Or implicitly:
FROM
tblAuthor AS a,
tblBook AS b
In either case, the relevant query would show all of the authors, and for each author, all of the books. Here's a full query:
-- show every book and every author
SELECT
a.FirstName + ' ' + a.LastName
AS 'Author name',
b.BookName
FROM
tblAuthor AS a CROSS JOIN
tblBook AS b
ORDER BY
'Author Name',
b.BookName
And here's what this would show:
The query shows 36 rows: for each of the 4 authors we see each of the 9 books listed. This isn't a sensible thing to see!
If you have a table of years and a table of months, I did once find it useful to create a query showing every possible combination:
Table of months | Table of years |
Given the two tables above, I showed every possible combination of month and year using a cross join:
The first few rows from the query below (which would generate 12 x 3 = 36 rows).
Here is the query to show the month/year combinations above:
-- show all possible month/year combinations
SELECT
m.MonthName + ' ' + CAST(y.YearNumber AS char(4))
AS MonthYear
FROM
tblMonth AS m
CROSS JOIN tblYear AS y
ORDER BY
y.YearNumber,
m.MonthNumber
If two tables have m and n rows respectively, a cross join between them will always generate m x n rows.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.