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!

  1. Inner Joins, Outer Joins and Shake-it-all-about Joins
  2. Creating Joins using your Mouse - the Query Editor
  3. Writing Inner Joins in SQL
  4. Full, Left and Right Outer Joins in SQL
  5. Cross Joins using SQL (this blog)
  6. 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:

Output of rows from cross join query

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 12 months Table of 3 years
Table of months Table of years

Given the two tables above, I showed every possible combination of month and year using a cross join:

Month and year combinations

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.

  1. Inner Joins, Outer Joins and Shake-it-all-about Joins
  2. Creating Joins using your Mouse - the Query Editor
  3. Writing Inner Joins in SQL
  4. Full, Left and Right Outer Joins in SQL
  5. Cross Joins using SQL (this blog)
  6. Self-Joins in SQL (joining a table to itself)
This blog has 0 threads Add post