This page has 0 threads | Add post

Wise Owl guides and tutorials

Writing Inner Joins in SQL

Written by Andy Brown

In this tutorial

Starting to Create the Query
A Digression on Alias Names
Creating the Join
Choosing Columns to Include
Joining more than 2 tables together
Joining tables from different databases
Using WHERE as an Alternative to INNER JOIN
The Limitations of Inner Joins

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:

FROM
    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 this tutorial!

Previous
tutorial

Dates and times

Previous
part

Creating Joins using your Mouse - the Query Editor

Top of page

Next
part

Full, Left and Right Outer Joins in SQL

Next
tutorial

Grouping data

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Joins

Writing Inner Joins in SQL

Starting to Create the Query

A Digression on Alias Names

Creating the Join

Choosing Columns to Include

Joining more than 2 tables together

Joining tables from different databases

Using WHERE as an Alternative to INNER JOIN

The Limitations of Inner Joins

Head office

London

Manchester