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


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 (8)

  1. bhargav says:

    need to take some duplicate valus and give the examples

    Thanks

    Bhargav

  2. Phil Mc says:

    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.

  3. jurilan says:

    how can we save the results in another table in the database?

  4. Vikas says:

    This is the most simplest way one has explained Joins ever!!!!

    Brilliantly done Sir. Thanks a lot.

  5. Carlos Ortega says:

    Thanks so much it helped me a lot!!!!

  6. wollek says:

    after years asking fellow team mates to do this for me, looks I am getting there myself now! Thanks bunch! – Nicely done!

  7. samta says:

    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.