Writing inner joins and outer joins in SQL
Part three 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 (this blog)
  4. Full, Left and Right Outer Joins in SQL
  5. Cross Joins using SQL
  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.

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.

Merged data from 2 tables

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:

AuthorId column in two tables

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:

SELECT statement start

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:

Four tables to do with films

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!

This blog has 0 threads Add post