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
- Full, Left and Right Outer Joins in SQL
- Cross Joins using SQL
- 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:
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:
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.
- Inner Joins, Outer Joins and Shake-it-all-about Joins
- Creating Joins using your Mouse - the Query Editor
- Writing Inner Joins in SQL
- Full, Left and Right Outer Joins in SQL
- Cross Joins using SQL
- Self-Joins in SQL (joining a table to itself) (this blog)