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 (this blog)
- Full, Left and Right Outer Joins in SQL
- Cross Joins using SQL
- 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.
Writing Inner Joins in SQL
OK, so you're ready to bite the bullet and start writing some SQL to join some tables together. Here's how to proceed.
We want to display columns from the tblAuthor and tblBook table as if they were joined together.
We need to tell SQL that the two tables share a value for the AuthorId column:
We'll show those records which share the same value for the AuthorId column in the two tables.
Starting to Create the Query
To begin with, choose one or other of the tables as the main one:
For an inner join it doesn't matter whether we start with the tblAuthor table and link this to the tblBook table, or vice versa - either will work.
Below we'll assume that we've made the books table the main one - and we've also given it an alias:
SELECT
FROM
tblBook AS b
You don't have to give tables aliases like this, but I think it makes joins easier to create and understand. If you do give a table an alias, you must use that at every other point in the query (you can no longer refer to the table by its real name).
A Digression on Alias Names
There seem to be 4 commonly-used conventions for aliases for tables in SQL:
| Convention | tblBook name | tblAuthor name |
|---|---|---|
| Use successive letters of the alphabet | a | b |
| Use initial letters, where possible | b | a |
| Use short codes | book | author |
| Don't use aliases at all | tblBook | tblAuthor |
Out of these, the first always seems ridiculous to me (who can remember which is table a, b or c?), so I tend to go for the second (as below) or the third.
Creating the Join
You now need to join the books table to the authors one. The syntax is:
tblTable1 AS Table1Alias
INNER JOIN tblTable2 AS Table2Alias
ON Table1Alias .ColumnName = Table2Alias.ColumnName
Here's our example SQL:
SELECT
FROM
-- link the book and author tables
tblBook AS b
INNER JOIN tblAuthor AS a
ON b.AuthorId=a.AuthorId
As mentioned above, we could just as easily join the tables in the other order:
SELECT
FROM
-- link the author and book tables
tblAuthor AS a
INNER JOIN tblBook AS b
ON a.AuthorId=b.AuthorId
Choosing Columns to Include
Now that you've taken care of the join, you can include any columns you like, providing that you prefix them with the correct aliases. Our query will read:
SELECT
-- show required columns
a.FirstName,
a.LastName,
b.BookName
FROM
-- link the book and author tables
tblBook AS b
INNER JOIN tblAuthor AS a
ON b.AuthorId=a.AuthorId
Don't forget: once you've given a table an alias, you can't refer to it by its original name, so it's a.FirstName, and not tblAuthor.FirstName.
It's worth mentioning that strictly speaking you don't need to use table alias prefixes in the above example, because there's no ambiguity (for example, the FirstName column only belongs to the table of authors, so we don't need to refer to it as a.FirstName - just FirstName would do). However, using table aliases as prefixes for every column is a good habit to get into, and in my mind makes it clearer what's going on.
Joining more than 2 tables together
If you want to join several tables together, you just need to create more inner joins. As before, these can come in any order. Suppose for example we want to show columns from 4 different tables for a films database:
Suppose we want to show for each film its language, studio and director.
You could accomplish this with the following SQL (here I've used longer, more meaningful table aliases):
SELECT
-- show one column from each table
studio.StudioName,
film.FilmName,
director.DirectorName,
language.Language
FROM
-- arbitrarily, start with table of directors
-- (we have to start somewhere!)
tblDirector AS director
-- join this to the table of films
INNER JOIN tblFilm AS film
ON director.DirectorID = film.FilmDirectorID
-- join this to the table of languages
INNER JOIN tblLanguage AS language
ON film.FilmLanguageID = language.LanguageID
-- join this to the table of studios
INNER JOIN tblStudio AS studio
ON film.FilmStudioID = studio.StudioID
Joining tables from different databases
There's nothing to stop you joining tables from different databases by preceding their table names with the database name and schema. For example:
SELECT
a.FirstName,
a.LastName,
b.BookName
FROM
-- link the book and author tables from different databases
Books.dbo.tblBook AS b
INNER JOIN Bookshop.dbo.tblAuthor AS a
ON b.AuthorId=a.AuthorId
Using WHERE as an Alternative to INNER JOIN
I don't recommend it at all, but you can also create a join between two tables using a WHERE clause:
-- avoiding using joins
SELECT
a.FirstName,
a.LastName,
b.BookName
FROM
tblAuthor AS a,
tblBook AS b
WHERE
a.AuthorId=b.AuthorId
This uses a cross join (described towards the end of this blog series), and has little to recommend it - I've included it for the sake of completeness.
The Limitations of Inner Joins
All inner joins will only consider records whose field values match each other. So every query on this page will miss out:
- Any authors who haven't written books; and
- Any books for whom there aren't any corresponding authors.
To remedy this, we'll need to create something called an outer join - the subject of the next part of my blog!