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:
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:
FROM
tblTable1 AS Table1Alias
INNER JOIN tblTable2 AS Table2Alias
ON Table1Alias .ColumnName =
Table2Alias.ColumnName
Here's our example SQL:
SELECT
FROM
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
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
a.FirstName,
a.LastName,
b.BookName
FROM
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
studio.StudioName,
film.FilmName,
director.DirectorName,
language.Language
FROM
tblDirector AS director
INNER JOIN tblFilm AS film
ON director.DirectorID = film.FilmDirectorID
INNER JOIN tblLanguage AS language
ON film.FilmLanguageID = language.LanguageID
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
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:
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!
