Multiple Joins Work just like Single Joins

Home > Blogs > SQL Server > Multiple Joins Work just like Single Joins

Multiple Joins Work just like Single Joins

7 39 Peter Avila
Added by August 18, 2014

Before reading this article, you should have a good understanding of single joins between two tables and be clear on the differences between inner and outer joins. Check out my previous post A Primer on Joins to help you accomplish this.

Have you ever looked at a query like the one below and wondered how to read it, how the different joins work together, and what aliens on what planet wrote such a thing?


Queries with multiple joins like this one often lead to confusion, such as the one behind this question that I have often heard from students: “There seems to be three tables joined to the Employee table in this query—two are inner joins and the other is an outer join. How can the same table have its non-matching rows eliminated and preserved at the same time in the same query?”

In this article, I will show you that confusions like this one arise from a syntax that encourages us to misunderstand joins, and I’ll offer another way of looking at multiple-join queries that makes questions like the one above melt away.

So, what leads to the confusion? Initially, it might seem that every table that is joined to this query is joined to a previous table; the ON clause suggests this with its references to columns in previous tables.


But this is not what actually happens in a multi-join query, and so looking at things in this way will lead to head-scratching.

So, what does a multi-join query actually do? It actually does something very simple. It performs a series of incremental, single joins between two tables at a time (while this article refers only to tables for simplicity sake, joins can be between tables, views, table valued functions, CTEs, and derived table subqueries). Each single join produces a single derived table (DT) that is then joined to the next table and so on. Like this:


JOIN 1: Inner join between Employee and Contact resulting in a derived table, DT1. Because this is an inner join, rows in Employee are excluded if they don’t match any rows in Contact, and vice-versa.

JOIN 2: Outer join between DT1 and JobCandidate resulting in a derived table, DT2. Because this is a left outer join, all rows in DT1 are preserved.

JOIN 3: Inner join between DT2 and SalesPerson resulting in a derived table, DT3. Because this is an inner join, rows in DT2 are excluded if they don’t match any rows in SalesPerson, and vice-versa.

JOIN 4: Outer join between DT3 and SalesOrderHeader resulting in a derived table, DT4. Because this is a left outer join, all rows in DT3 are preserved.

JOIN 5: Outer join between DT4 and SalesTerritory resulting in a derived table, DT5. Because this is a left outer join, all rows in DT4 are preserved. DT5 is the final result of the query.

So, what about that confusion arising from the ON clause? With this new way of looking at multiple-join queries, we can now see that the proper way to read an ON clause is not that it joins the new table to a single table that came before it in the query! The only join that does that is the first one; all subsequent ones join a new table to the derived table that is a result of all the joins before it. If an ON clause includes a table alias, that is only to identify the column properly to the query. Table aliases are only required when there is ambiguity—when two or more columns have the same name in the derived table that precedes the current join because they came from different tables.


I hope this new way of looking at multiple-join queries helps make it easier and more productive for you to work with joins.

Have fun!

Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 153 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 487 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 608 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

See what people are saying...

  1. Avatar Nick

    I’m new to SQL. Two questions:

    1. Is it correct to say: as more joins are applied, the resulting derived table gets progressively Wider and Shorter? (more columns, less rows)

    2. I’m assuming this applies to all SQL (including Oracle)?

  2. Avatar Billal BEGUERADJ

    I stumbled on a complex MySQL query and your article helped me to simplify it.
    Billal BEGUERADJ

  3. Avatar claude loch

    Each query may comprise zero, one, or more joins. A multiple join is a use of more than one join in a single query. The joins used may be all of the same type, or their types can differ. We’ll begin our discussion by showing an example query that uses two joins of the same type. Take a look at the query below.

  4. Avatar Enrique

    Thanks! It was so clear! Good work!

  5. Avatar deepak

    such a complex thing explained in very simple words. simply amazing…

  6. Avatar Amol Padgalwar

    Your are simply amazing,thanks a lot for this article,please write more on like this on procedures,more complex joins,sub query, triggers

  7. Avatar Murat YILDIZ

    I have never seen such a kind of perfect explanations regarding to Joins in SQL with proper figures that explains the logic very good. There should be much more articles regarding to the other topics by this Author 🙂 Thanks a lot…

  8. Avatar Remo Ferrone

    Thank you Peter,
    I’ve been trying to raise my SQL skills after being reliant on the MS Access QBE for far too long. I was trying to mentally parse out the elements of the join for multiple tables. Your well structured demonstration was exactly what I was looking for and will go a long way towards improving my understanding and skill without visual aids.

  9. Avatar Prabuddha Kulatunga

    Nice explanation. Thank you very much!

  10. Avatar Nik

    Mate – I’ve been trying to wrap my head around this concept all day and your diagram was so helpful.

    Thank you.


  11. Avatar Kevin

    Great article. Thank you. Once question…what happens to the previous DT once a new DT is created? Does it hang around in memory or is it collected with the rest of the garbage?

  12. Avatar Sam

    Thanks much for the derived table illustration, it helped me connect the dots. Really appreciate it!

  13. Avatar Prudence

    many thanks!. this helped me understand why I was getting undesirable results with my query.

  14. Avatar Vijay Rajgor

    Thanks it is very help to undsrstand how join works internally.

  15. Avatar Abraham Brookes

    M8000, this is the best explanation of joins EVER. It FINALLY clicked! Thank you so much!

  16. Avatar Ronald Wahome

    This was very helpful. Very easy to understand when you think of it as an entirely new table after every line of code!

  17. Avatar snehal sawant

    Could you please provide the details of all tables(table structure) and what would be the desired output ?

  18. Avatar Hareesh

    Eespicially this kind multiple table joins frequently used in BI data warehouse projects. recently i worked on this kind of joins.. few confusions got cleared with reading this article. Great thanks Peter :).

  19. Avatar Yash

    An informative and enlightening article. Really cleared the remaining doubts. Thank you Peter.

  20. Avatar Nipun Kumar

    Excellent article. Saved me from lots of confusion

  21. Avatar Petro

    Thanks. Excellent explanation!

  22. Avatar John

    Thanks from Warner Robins, Georgia. This really cleared some things up for me.

  23. Avatar vadivel murthy

    execellent explanation… thank you so much

  24. Avatar Karthikeyan N

    Perfect explanation.. I kept scratching my head about multiple joins until I came across this…

  25. Avatar Saumya Ranjan

    Thanks for the to-the-point explanation! Precise and Perfect, especially the pictures. Great work!!

  26. Avatar Akhilesh

    Thank you !!!!

  27. Avatar Mahesh khatai

    Thanx for the article . Helped me a lot in understanding the multiple table join .

  28. Avatar pradip

    Thanks for the good article.

    But I think there is confusion in Derived table image,
    DT1 should be in blue arrow instead of orange color.

    For reference, I am attaching image.

  29. Avatar Mbuku Ditutala

    I love people that make others’ lives easy and that’s the case. Thanks for clearing our doubts.

  30. Avatar Neil

    As someone new to SQL and just starting to need to join multiple tables, this is exactly what I needed. Especially the picture. Thank you very much for the clarity!

  31. Avatar David Aspden

    Awesome guide, thank you. It really makes it clear what happens when joining multiple tables. In regards to a “where” clause, this clause I think just limits the final result set by eliminating those records that don’t match?

  32. Avatar Debra

    Thank you very much! The illustration really helped 🙂

  33. Avatar Alex

    great!!! It’s easier to understand now how joins works!!!

  34. Avatar Pete Dunham

    Very good explanation! Many thanks.

  35. Avatar Neeraj Sehtya

    Thank you very much for sharing such a brief yet descriptive paper on joins. I had always wanted to know the modus-operandi of joins and this is the best resource that I have seen to explain it. Going to share it further with people who are struggling to understand this.

  36. Avatar Abebe

    Thank you very much. I had a big confusion how multiple join works. Now, it is very clear to me.

  37. Peter Avila Peter Avila

    Hi, Alexis. The result of a join is always a single virtual table. But so is the result of a query with no joins and only a single table! It doesn’t matter how many tables you have in your query—1 or many joined together—the result will always be a single virtual table with all of the columns from all of the tables. The filter you create in your WHERE clause can reference any one of those columns to filter out data from the virtual table. So, the affect that a WHERE clause has on a join is the same as it has on a single table: it filters out rows of the resulting virtual table—whether that virtual table is derived from a single table or a multitude of joins—that don’t pass the condition in the WHERE clause.

    I hope this answers your question. Please let me know if it doesn’t.


    Peter Avila

  38. Avatar Alexis

    what happens when we add at the end of that query a where clause ! How the where affect the join operations between tables!? Thanks!

  39. Avatar Sridhar

    Many thanks!This topic helped me very much in clearing the confusion in multiple type joins in one query.Thanks agiain.

Share your thoughts...

Please fill out the comment form below to post a reply.