Writing inner joins and outer joins in SQL
Part six 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
  6. Self-Joins in SQL (joining a table to itself) (this blog)

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.

Self-Joins in SQL (joining a table to itself)

A self-join is when a table contains a link to itself.

Examples of Tables which Reference Themselves

Occasionally in SQL Server you find a table which references itself.  Here's an example:

Table of films with sequels

In this table, each film contains a pointer to its sequel number.

 

Another typical example is where you have a table of employees, each of whose records contains a field pointing to that table's immediate line manager (themselves also records in the table).

Creating a Self-Join

The secret to a self-join is to create good aliases.  Here's an example query for the table above showing for each book its immediate prequel and sequel:

-- show books with their prequels and sequels

SELECT

book.BookName AS Book,

prequel.BookName AS Prequel,

sequel.BookName AS sequel

FROM

tblBook AS book

LEFT OUTER JOIN tblBook AS prequel

ON prequel.SequelBookId=book.BookId

LEFT OUTER JOIN tblBook AS sequel

ON book.SequelBookId=sequel.BookId

ORDER BY

book.BookName

Here are the rows that this query would show:

List of books with prequel and sequel

The books, prequels and sequels from the above query.

Notice that by using aliases, we can effectively treat each instance of the tblBook table as if it were a different table.

  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
  6. Self-Joins in SQL (joining a table to itself) (this blog)
This blog has 0 threads Add post