Share via

The simplification of JOIN. Simple example of how T-SQL JOIN work

  • Article

Today I’ll try to explain how Joins work. If you are a DBA or a T-SQL expert, this is no news to you.

But I’m not, and I’m sure there is a lot of people out there who are in the same situation.

For example, sometimes there may be problems and you need to examine auto generated SQL, and there you have a Join.

When I see a join I know what it does, i.e. exactly what it says on the tin. It joins two tables in order to create one resultset.

However, I can never remember what the left join means, or how a right join works.

So I always have to consult BOL and perform some tests in order to get into the right mind.

In the end I created a super simple sample that demonstrates joins. And I thought I’d share it.

The outlay is simple; you have a table with authors and a table with books. Now, some authors may not yet have written

a book, some books may not have an author (perhaps written under pseudonym) and some books may have more than one author.

So, under certain situations you may wish to get a list of all the existing authors and their books. If they have not written a book, you still want them.

Or you wish to have a list of all existing books, even the ones without an author.

Or you want a list of all authors, including the ones who has not written a book, AND all books, even if they have no author.

Or you want a list that only contains authors that have written a book, and the other way around, it should only contain books that has an author.

This is where joins come into play.

Fire up SQL Server Management Studio and create these two tables.

-- Create Author table and insert some rows.

create table Authors(AuthorId int primary key, AuthorName nchar(30) not null)

insert into Authors values (1, 'Mr Yellow')

insert into Authors values (2, 'Mr Blue')

insert into Authors values (3, 'Mr Food')

insert into Authors values (4, 'Mr NoBook')

-- Create Books table and insert some rows.

create table Books( BookId int not null, AuthorId int not null, BookTitle nchar(30),

constraint [PK_Books] primary key clustered( BookId, AuthorId ))

insert into Books values (1, 1, 'The Green Book')

insert into Books values (1, 2, 'The Green Book')

insert into Books values (2, 3, 'Cooking Alone')

insert into Books values (3, 0, 'The Authorless Book')

--------------------------------------------------------------------------------------

The Author table contains four authors, but Mr. NoBook has not yet written any books.

The Book table contains three books, one (The Green Book) is co written by Mr. Yellow and Mr. Blue, and one doesn’t have a listed author.

Think of the tables placed side by side, the Author table to the left and the Book table to the right.

AuthorId AuthorName

----------- ----------------------

1 Mr Yellow

2 Mr Blue

3 Mr Food

4 Mr NoBook

BookId AuthorId BookTitle

----------- ----------- -----------------

1 1 The Green Book

1 2 The Green Book

2 3 Cooking Alone

3 0 The Authorless Book

Basically, this is how it goes.

LEFT JOIN - Include all authors, including the ones with no books.

RIGHT JOIN - Include all books, including the ones without an author.

FULL JOIN - Include all authors and all books, match them where there is a match, and include the ones without a match.

JOIN - Include only author/books that match. Leave out the authors with no books and books with no author.

So let’s do the scenarios one by one.

In the first scenario, you want a list of all the existing authors and their books. If they have not written a book, you still want the author.

Here, you use a LEFT Join.

-- LEFT. All authors, including the ones with no books.

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

left join Books b on a.AuthorId = b.AuthorId

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

4 Mr NoBook NULL NULL

In the second scenario, you want a list of all the existing books and their authors. If they do not have an author, you still want the book.

Here, you use a RIGHT Join.

-- RIGHT. All books, including the ones with no authors.

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

right join Books b on a.AuthorId = b.AuthorId

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

NULL NULL 3 The Authorless Book

In the third scenario, you want a list of all the existing authors and their books, if any. And you also want all the books, and their authors, if any.

Here, you use a FULL Join.

-- FULL. All authors, including the ones with no books and all books, including the ones with no authors.

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

full join Books b on a.AuthorId = b.AuthorId

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

4 Mr NoBook NULL NULL

NULL NULL 3 The Authorless Book

In the fourth scenario, you want a list of all the existing authors that has written a book and all books that has an author for it.

Here, you just use a Join.

-- JOIN. Only authors with books, or books with authors, all rows that has an entry in both left and right.

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

join Books b on a.AuthorId = b.AuthorId

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

So there you have it, joins. Whenever I need to understand joins but not necessarily have to remember how they work, I just run all SQL

above in one go, and then have a look at the outcome, and it is visually obvious what they do.

create table Authors(AuthorId int primary key, AuthorName nchar(30) not null)

insert into Authors values (1, 'Mr Yellow')

insert into Authors values (2, 'Mr Blue')

insert into Authors values (3, 'Mr Food')

insert into Authors values (4, 'Mr NoBook')

create table Books( BookId int not null, AuthorId int not null, BookTitle nchar(30),

constraint [PK_Books] primary key clustered( BookId, AuthorId ))

insert into Books values (1, 1, 'The Green Book')

insert into Books values (1, 2, 'The Green Book')

insert into Books values (2, 3, 'Cooking Alone')

insert into Books values (3, 0, 'The Authorless Book')

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

left join Books b on a.AuthorId = b.AuthorId

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

right join Books b on a.AuthorId = b.AuthorId

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

full join Books b on a.AuthorId = b.AuthorId

select a.AuthorId, a.AuthorName, b.BookId, b.BookTitle

from Authors a

join Books b on a.AuthorId = b.AuthorId

drop table Authors

drop table Books

Result

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

4 Mr NoBook NULL NULL

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

NULL NULL 3 The Authorless Book

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

4 Mr NoBook NULL NULL

NULL NULL 3 The Authorless Book

AuthorId AuthorName BookId BookTitle

----------- ------------------------------ ----------- ------------------------------

1 Mr Yellow 1 The Green Book

2 Mr Blue 1 The Green Book

3 Mr Food 2 Cooking Alone

Comments

  • Anonymous
    May 26, 2009
    The comment has been removed

  • Anonymous
    January 23, 2013
    need to take some duplicate valus and give the examples Thanks Bhargav

  • Anonymous
    February 08, 2013
    Nicely done.  I think many like my self need help in learning how to run a complex query one inner query at a time.  Anyway, nice job.

  • Anonymous
    February 15, 2013
    how can we save the results in another table in the database?

  • Anonymous
    May 10, 2013
    This is the most simplest way one has explained Joins ever!!!! Brilliantly done Sir. Thanks a lot.

  • Anonymous
    January 23, 2014
    Thanks so much it helped me a lot!!!!

  • Anonymous
    March 28, 2014
    after years asking fellow team mates to do this for me, looks I am getting there myself now! Thanks bunch! - Nicely done!

  • Anonymous
    April 08, 2015
    I have go through many website to understand join logic and it was not clear. Thanks sir to give very good example and simple way to understand join.