BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
An in-depth SQL tutorial on how to create inner joins, left outer joins, right outer joins, full outer joins, cross joins and self-joins!
- Inner Joins, Outer Joins and Shake-it-all-about Joins
- Creating Joins using your Mouse - the Query Editor
- Writing Inner Joins in SQL
- Full, Left and Right Outer Joins in SQL
- Cross Joins using SQL (this blog)
- Self-Joins in SQL (joining a table to itself)
This online training on joins is part of a longer tutorial on the whole of SQL. Naturally, Wise Owl also run a range of SQL training courses for individualas and (above all) for businesses.
Posted by Andy Brown on 30 November 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Cross Joins using SQL
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).
What Cross Joins Do
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!
A Possible Use for Cross Joins
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.