557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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 ...
Writing inner joins and outer joins in SQL Part five of a six-part series of blogs |
---|
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!
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. |
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.
Parts of this blog |
---|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.