562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
A self-join is when a table contains a link to itself.
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).
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
book.BookName AS Book,
prequel.BookName AS Prequel,
sequel.BookName AS sequel
tblBook AS book
LEFT OUTER JOIN tblBook AS prequel
LEFT OUTER JOIN tblBook AS sequel
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.
|Parts of this blog|
25 Aytoun Street