SQL Multiple Joins – How to Join Tables the Easy-Breezy Way 

Have you ever found yourself tangled in the web of SQL multiple joins? 

Don’t sweat it – we’ve all been there. Think of it like a puzzle, with tables as puzzle pieces. You’re just connecting them to see the bigger picture. 

Now, I know, the code might look like a maze, and it’s easy to get lost. But guess what? Even the best adventurers took some wrong turns at first. And as for that performance bogeyman? It’s like worrying about your first day at a new school – totally natural. 

Imagine SQL joins as making friends at a party. You chat, find common interests (matching data), and form groups (result sets). Sure, sometimes you’ll meet a tricky person (complex query). But it’s smooth sailing once you strike a conversation (proper code). 

So, newbie SQL champs, take a deep breath. It isn’t rocket science; it’s more like discovering a hidden treasure. 

In this blog post, we’ll be your guide and help you navigate the world of SQL multiple joins with a friendly hand and a bunch of cool tricks. Let’s get ready to level up your SQL adventure! 

Before we begin, let me give you a heads-up on how we will tackle it: 

• How to do SQL joins like matches made in heaven
• Crafting SQL multiple joins without scratching your head
• Types of SQL multiple joins + examples
• Common mistakes with SQL multiple joins
• Advanced concepts with SQL multiple joins
• Performance considerations with SQL multiple joins
• Tools for working with SQL multiple joins

So, grab your coffee, and let’s begin! 

How to Do SQL Joins Like Matches Made in Heaven 

We’re not looking for your next date using a matchmaker. Instead, you will be the matchmaker – not of people but of tables. 

But first, I know you might find this sort of code boring already: 

SELECT column1, column2 FROM table 

Since there is nothing else you can do with that, it becomes imperative to jump to a whole new level – using SQL joins. 

Joining tables in SQL is the fundamental essence that makes databases like SQL Server relational. So, mastering them should be a part of your journey to SQL excellence. And we’re starting here because moving to SQL multiple joins would be a complex challenge without a solid understanding of how they work.This won’t be tricky, I promise. 

To start, let’s talk about movies, particularly movies with time-travel elements on them. Take a look at the data we need to match below: 

Looks fun. We’ll be matching them like a game. If you watched these eight movies, you know who stars in them. 

Let’s begin pairing them together. 

INNER JOIN 

What to Expect: An INNER JOIN will display data on both tables with matching keys. In other words, those without a match will not appear. 

So, let’s apply it to the movies and actors tables. 

We need to list down all the movies with their actors. Here’s the result we get using an INNER JOIN: 

Of the eight movies we have, only seven share actors in common. If you look back at the image earlier, Groundhog Day (1993) didn’t make it to our list. 

What about the actors? Hugh Jackman and Patrick Stewart were excluded from the result. If we only have X-men: Days of Future Past (2014), another time-travel hit, they’ll be on that list. 

Basic Syntax 

SELECT <column_list> 
FROM Table1 
[INNER] JOIN Table2 on Table1.keycolumn = Table2.foreignkeycolumn 
... 

The glue of SQL joins is primary and foreign keys. Your table designs should have them to identify join columns at once. It’s like having your keys for the car, door, and whatever else in the right place which is easy to remember. 

But you can omit the INNER keyword and leave it with JOIN only. For clarity, this article will keep the INNER keyword. 

So, to retrieve data from the movies and actors table, execute the SELECT statement with a simple INNER JOIN included: 

SELECT movies.title, actors.actor_name 
FROM movies 
INNER JOIN actors on movies.movie_id = actors.movie_id 

The output shows which actor appeared in which movie. Let’s explain the statement: 

• SELECT movies.title, actors.actor_name: Indicates the names of movies and actors, who play in them, you want to get in the result. 
• FROM movies: Instructs SQL Server to retrieve a list of movies from the movies table. 
• INNER JOIN actors on movies.movie_id = actors.movie_id: Defines the key to get matching rows on both tables. 

LEFT JOIN 

What to Expect: A LEFT JOIN will display the rows from the LEFT table whether they have a match on the right table or not. You can identify the left table in a query when it appears immediately after the FROM keyword, while the right table follows the LEFT JOIN keywords. This type of join is also commonly referred to as a LEFT OUTER JOIN. 

In our movie data, a LEFT JOIN will list all the movies, whether or not they have associated actors: 

It’s almost the same as the INNER JOIN with one very obvious difference. Look at the Groundhog Day (1993) movie. We have put it in a red box above. It is stored in the movies table, but there are no corresponding entries in the actors table for that particular movie. LEFT JOIN displays the rows of the LEFT table even if there are no matching rows in the right table. So, Groundhog Day (1993) made it to the list. 

Basic Syntax 

SELECT <column_list> 
FROM Table1 
LEFT JOIN Table2 ON Table1.keycolumn = Table2.foreignkeycolumn 

Example: 

SELECT movies.title, actors.actor_name 
FROM movies 
LEFT JOIN actors on movies.movie_id = actors.movie_id 

As you see, we’re only replacing INNER with LEFT. But the result is different. We will do the same for the rest of the examples. 

So, listing movies with or without their actors is painless. Let’s explain: 

• SELECT movies.title, actors.actor_name: Indicates the names of movies and actors, who play in them, you want to get in the result. 
• FROM movies: Defines the name of the LEFT table. 
• LEFT JOIN actors ON movies.movie_id = actors.movie_id: Returns rows from the left table with or without matching rows in the right table. And actors is the name of the RIGHT table. 

RIGHT JOIN 

What to Expect: This is the reverse of LEFT JOIN. When flipped, it will list rows in the RIGHT table with or without the matching rows in the left table. Again, the left table is the table specified in the FROM clause, while the right table is the table set in the RIGHT JOIN clause. This type of join is also commonly referred to as a RIGHT OUTER JOIN. 

So, we can compare this to flipping coins. One side of the coin is the head (LEFT JOIN). When flipped (RIGHT JOIN), you see the tails. We can illustrate it like using the movies and actors data. 

Basic syntax 

SELECT <column_list> 
FROM Table1 
RIGHT JOIN Table2 ON Table1.keycolumn = Table2.foreignkeycolumn 

Example: 

SELECT movies.title, actors.actor_name 
FROM movies 
RIGHT JOIN actors on movies.movie_id = actors.movie_id 

Let’s explain the statement: 

• SELECT movies.title, actors.actor_name:  Indicates the names of movies and actors, who play in them, you want to get in the result. 
• FROM movies:  Defines the name of the table from which a list of movies will be retrieved. 
• RIGHT JOIN actors ON movies.movie_id = actors.movie_id:  Returns rows from the right table with or without matching rows in the left table.  The result is different from both INNER JOIN and LEFT JOIN. 

FULL JOIN 

What to Expect:  The result is a combination of LEFT and RIGHT JOINs. It shows everything from the left and right tables, whether they have matching rows or not. This type of join is also commonly referred to as a FULL OUTER JOIN. 

Check out the illustration below with the movies and actors data. 

Did you see the boxed rows above in red? We saw them earlier in both LEFT and RIGHT JOINs. 

Basic syntax 

SELECT <column_list> 
FROM Table1 
FULL JOIN Table2 ON Table1.keycolumn = Table2.foreignkeycolumn 

Example: 

SELECT movies.title, actors.actor_name 
FROM movies 
FULL JOIN actors on movies.movie_id = actors.movie_id 

Let’s explain the statement: 

• SELECT movies.title, actors.actor_name: Indicates the names of movies and actors, who play in them, you want to get in the result. 
• FROM movies: Defines the name of the table from which a list of movies will be retrieved. 
• FULL JOIN actors ON movies.movie_id = actors.movie_id: Defines a key to unlock all data, whether there are matching rows or not on both left and right tables. 

Self Join 

What to Expect: You will join a table to itself using either INNER, LEFT, RIGHT, FULL, or CROSS JOIN. Results depend on what type of join you used. A self join is like talking to yourself. 

Let’s say you want to list movies with sequels. To make it happen, the movies table should identify a sequel id. Here’s what the output might be: 

The above example only needs the movies table for the left and right tables. 

Basic syntax 

SELECT <column_list> 
FROM Table1 a 
INNER JOIN Table1 b ON a.primary_key = b.secondary_key 

You need to use a table alias to differentiate the left and right tables. We used ‘a’ and ‘b’ aliases here.  

Example: 

SELECT 
 a.title 
,b.title 
FROM movies a 
INNER JOIN movies b ON a.movie_id = b.sequel_id 

Let’s explain the statement: 

• Both the left and right tables use the movies table. 
• FROM movies a: Defines the left table. 
• INNER JOIN movies b: Defines the right table. 
• a.movie_id = b.sequel_id: movie_id is the primary key, while sequel_id is the secondary key within the same table. This is NULL if a movie has no sequel. 

CROSS JOIN 

What to expect: Imagine a crossroads where all paths meet. It connects every row from one table with every row from another, making a big combo of information. 

So, if you want to know combinations within your data, for example, two possible actor team-ups for the next movie, use a CROSS JOIN. 

Here is all possible team-ups with Chris Hemsworth retrieved from the actors table: 

Basic syntax 

SELECT <column_list> 
FROM Table1 a 
CROSS JOIN Table2 

You don’t need to specify column keys to join them. SQL Server will take care of the combinations. 

Example: 

SELECT 
 a.actorName 
,b.actorName 
FROM Actors a 
CROSS JOIN Actors b  
WHERE a.actorName <> b.actorName 

• We used a self join and a CROSS JOIN. 
• Actors a and Actors b: Indicates the table aliases such as ‘a’ and ‘b’. 
• CROSS JOIN Actors b: Combines information based on the actor’s name. 
• WHERE a.actorName <> b.actorName: Sets the condition based on which the data will be filtered and retrieved. It is recommended to avoid matching a name to itself. So, Chris won’t team up with himself unless the movie is about twins. 

Loving joins so far? Here’s a summary: 

Join Type	What It’s Like	When to Use
INNER JOIN	Movies with their actors	When you only need to retrieve matching rows
LEFT JOIN	List of movies with actors	When you need to retrieve rows from the left and right tables
RIGHT JOIN	List of actors with movies	When you need to retrieve rows from the right table and  some rows from the left table
FULL JOIN	Mashup of movies and actors	When you need to get all data from both tables
SELF JOIN	Chatting with yourself	When you compare data within one table
CROSS JOIN	Paths crossing at a junction	When you want all combos of data

Hope this table makes the differences pop! 

Now, let’s head to SQL multiple joins. 

Types of SQL Multiple Joins + Examples 

SQL Multiple JOINS are like detectives solving a complex case. They help when you need specific, detailed answers from different tables like finding out which students aced a test, in which class, and who taught it. It is great for detailed reports and analysis. 

But there’s a catch. 

Doing multiple JOINS can be like juggling cats – tricky! If tables are huge or keys aren’t set right, it can slow down the query. In addition, reading complex results can feel like decoding a secret message. Debugging can be a puzzle on its own. 

But then again, there is something you can handle. 

Here are some real-world scenarios for multiple joins in SQL: 

1. E-commerce Analysis: Defining which customers bought which products along with order details. 

2. Sales Reporting: Finding out which salespeople sold which products in specific regions. 

3. Project Tracking: Identifying which employees worked on which projects along with their roles. 

When you use multiple joins in SQL, you will find at least two JOIN clauses in a query. In other words, it will use at least three tables. To break it down, it’s one table for the FROM clause and two tables for the JOIN clause. 

Let’s consider some examples. They all will use the following database design: 

It refers to the same movies and actors data. Also, a third table – MovieActors – is included. You see, some actors appear in more than one movie. If we stick to the previous design, we have to repeat storing the same actor data for this scenario. This will create duplicates, which is not a good practice. 

With the third table (MovieActors), we store actors once and reuse them in more movies. It also creates a relationship between movie titles and actors. It’s like a pile of marriage licenses. 

Now, let’s proceed. 

SQL Multiple INNER JOINs 

Here’s a multiple INNER JOIN example that returns all movies and actors who star in them: 

SELECT  
 a.title 
,a.releaseDate 
,c.actorName 
FROM Titles a 
INNER JOIN MovieActors b ON a.titleID = b.titleID 
INNER JOIN Actors c ON b.actorID = c.actorID 
ORDER BY a.releaseDate, c.actorName; 

It’s a double INNER JOIN! Here we joined the Titles, MovieActors, and Actors tables. It’s like having three snacks in a movie: a burger, bunch of chips, and popcorn. We also sort them by release dates and actor names. 

Here’s the result shown in dbForge Studio for SQL Server: 

SQL Multiple LEFT JOINs 

Here’s an example to display all the movies with or without their actors. 

SELECT  
 a.title 
,a.releaseDate 
,c.actorName 
FROM Titles a 
LEFT JOIN MovieActors b ON a.titleID = b.titleID 
LEFT JOIN Actors c ON b.actorID = c.actorID 
ORDER BY a.releaseDate, c.actorName; 

Check the result below: 

One movie made it to the list even without actors in it. 

SQL Multiple RIGHT JOINs 

Let’s list down all the actors with or without their movie titles. 

SELECT  
 a.title 
,a.releaseDate 
,c.actorName 
FROM Titles a 
RIGHT JOIN MovieActors b ON a.titleID = b.titleID 
RIGHT JOIN Actors c ON b.actorID = c.actorID 
ORDER BY a.releaseDate, c.actorName; 

The result would be as follows – some actors have no listed movies. 

SQL Multiple FULL JOINs 

Sometimes, we need to combine the results of both LEFT and RIGHT JOINs. Here’s an example with multiple FULL JOINs: 

SELECT  
 a.title 
,a.releaseDate 
,c.actorName 
FROM Titles a 
FULL JOIN b ON a.titleID = b.titleID 
FULL JOIN Actors c ON b.actorID = c.actorID 
ORDER BY a.releaseDate, c.actorName; 

Check out the result below: 

SQL Multiple Self Joins 

Suppose I add a new movie: Back to the Future Part III (1990). Here’s the code on how to list trilogy movies using a self join. 

SELECT 
 a.title 
,b.title 
,c.title 
FROM Titles a 
INNER JOIN Titles b on a.titleID = b.sequelID 
INNER JOIN Titles c on b.titleID = c.sequelID 

The output would be as follows: 

SQL Multiple CROSS JOINs 

Finally, let’s have 3-actor team-ups using CROSS JOIN: 

SELECT 
 a.actorName 
,b.actorName 
,c.actorName 
FROM Actors a 
CROSS JOIN Actors b  
CROSS JOIN Actors c 
WHERE a.actorName <> b.actorName AND 
      b.actorName <> c.actorName AND 
      a.actorName <> c.actorName; 

As you see, we joined the actors table twice with the condition that anyone won’t team up with themselves. The WHERE clause defines this non-repeating condition. The result set would be: 

Common Mistakes with SQL Multiple Joins 

Let’s tackle those common mistakes with some easy fixes! 

Missing ON Conditions 

• Mistake: Forgetting to specify the columns to join. 
• Fix: Include the proper ON conditions to connect the tables. 

Mistake Code: 

SELECT p.ProductName, c.CategoryName 
   FROM Products p 
   INNER JOIN Categories c; 

Correction:

SELECT p.ProductName, c.CategoryName 
   FROM Products p 
   INNER JOIN Categories c ON p.CategoryID = c.CategoryID; 

Duplicate Columns 

• Mistake: Selecting columns with the same name from different tables without aliases. 
• Fix: Use aliases to distinguish columns with the same name. 

Mistake Code: 

SELECT p.CategoryID, p.ProductName, c.CategoryID, c.CategoryName 
   FROM Products p 
   JOIN Categories c ON p.CategoryID = c.CategoryID; 

Correction:

SELECT p.CategoryID as ProductCategoryID, p.ProductName, c.CategoryID, c.CategoryName 
   FROM Products p 
   JOIN Categories c ON p.CategoryID = c.CategoryID; 

Too Many SQL Multiple Joins 

• Mistake: Joining multiple tables may lead to complex and slow queries. 
• Fix: Simplify your query or break it into smaller steps if needed. 

Mistake Code: 

SELECT 
 ps.Name AS ProductSubcategory 
,SUM(sod.OrderQty) AS TotalOrders 
FROM Production.Product p 
INNER JOIN Sales.SalesOrderDetail sod ON P.ProductID = sod.ProductID 
INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID 
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID 
WHERE soh.OrderDate BETWEEN '1/1/2014' AND '12/31/2014' 
AND p.ProductSubcategoryID IN (1,2) 
GROUP BY ps.Name 
HAVING ps.Name IN ('Mountain Bikes', 'Road Bikes') 

Correction: 

   — Break into separate queries if needed 

Mixed Join Types 

• Mistake: Mixing join types (e.g., INNER and LEFT) without clear reasoning. 

• Fix: Use join types intentionally based on your data requirements. 

Mistake Code: 

SELECT p.ProductName, o.OrderDate 
   FROM Products p 
   LEFT JOIN Orders o ON p.ProductID = o.ProductID 
   INNER JOIN Categories c ON p.CategoryID = c.CategoryID; 

It is syntactically correct. However, the INNER JOIN defeats the purpose of the LEFT JOIN. The result is the same as using multiple INNER JOINs. 

Correction: 

SELECT p.ProductName, o.OrderDate 

   FROM Products p 

   LEFT JOIN Orders o ON p.ProductID = o.ProductID 

   -- Consider if you really need the INNER JOIN here 

Remember:
Mistakes are like detours on a road trip – they happen. But you can find your way back with a little guidance. Stay calm, take it step by step, and you’ll be fixing those queries like a pro! 

Advanced Concepts with SQL Multiple Joins 

General Considerations for Joining More Than Two Tables 

• It’s like juggling more balls – things can get tricky, so keep it organized.
• Make sure you’ve got clear relationships between all the tables.
• Check if the database is designed for efficiency; large joins might slow things down.

Joining with Aggregate Functions 

• Think of it as counting apples in different baskets. Use aggregate functions (like SUM, AVG) wisely.
• Aggregates can change results, so group and summarize data logically.
• Keep an eye on performance – big data and aggregates can slow down your query.

Mix SQL Joins with Subqueries 

• Picture it as a treasure hunt with secret notes – subqueries dig out specific data.
• Use subqueries when you need to include a single value from other tables in your result set.
• Be cautious! Multiple subqueries can make your query feel more like a crazy maze.

Joining with Set Operators (UNION, INTERSECT, EXCEPT)

• It’s like merging two playlists – set operators combine and compare results. 
• UNION piles up info from different tables, INTERSECT finds overlaps, and EXCEPT shows what’s unique. 
• Make sure the columns match, and data types fit, just like songs in a playlist. 

Remember, the more tables you bring to the party, the more organized and focused you need to be to avoid a data jam! 

Performance Considerations with SQL Multiple Joins 

Let’s make it smooth for you: 

• The More Tables You Join, the Slower It Gets: Make sure you only include the needed tables. Tables you don’t really need are like gatecrashers in a party. You don’t want them at your party but they’re just there. You may consider breaking down the query into smaller chunks if it’s still slow. 
• Mind Your Table Sizes: Big tables can slow things down, like traffic jams on a busy highway. So, limit your results to the smallest possible result needed using a WHERE clause. 
• Indexes Are Your Friends: Think of them as maps for your database – they help find info faster. 
• Stay Organized: Keep your query lean and focused, like packing for a trip with only what you need. 

• Limit Extra Work: Just like tables, use only the columns you really need. It’s like avoiding carrying extra baggage. 
• Check Query Plan: Imagine it’s like a preview before a movie – it shows how the database will work. 
• Test with Sample Data: Try your query with a small chunk of data first, like a taste test before a big meal. 
• Consider Caching or In-Memory Tables: Just like pre-made sandwiches, caching can save time by reusing previous results. 

Remember, you’re on a journey to becoming a SQL master, and these tips will help you navigate with ease! Hungry for more SQL Server performance tips? Then, check out my article. 

Tools for Working with SQL Multiple Joins 

With practice, SQL multiple joins can become second nature to you. Piece of cake! 

But what if you can make it even easier to do? 

That’s where tools come in. GUI tools such as SQL Server Management Studio or dbForge Studio for SQL Server are top-of-the-line tools. In this article, I used dbForge Studio for SQL Server for the examples, namely its super easy SQL coding magic to give me code suggestions and completions. 

Another tool available in the Studio is a visual Query Builder. With it, you can think of tables like Lego pieces you drag. No more wrong spellings or forgotten keywords. It’s like a safety net that lets you reduce the errors in your SQL code. 

dbForge Studio for SQL Server makes it easier to create SQL multiple joins. A built-in Database Explorer allows you to navigate the database easily. Once you find the tables you need, you can drag them into the query builder canvas. 

If you have primary and foreign keys in place, the INNER JOIN type is pre-selected for you. You can still change it to another join type, though. 

In addition, you can add WHERE, GROUP BY, etc. All goes from a few clicks of your mouse. However, you can code if that’s your preference. 

See a screenshot of the visual Query Builder in dbForge Studio for SQL Server: 

SQL Multiple Joins is Not as Hard as You Think 

You begin your journey in SQL joins by learning about different types of joins and their output. From here, you can expand to SQL multiple joins by applying what you learned. How good your database design is will tell how easy it is for you to form those joins. 

There’s always a first time for everything. So, you can make mistakes at first, and that’s alright. With more practice, you can do your next multiple joins in SQL as easy as pie. 

You also witness an amazing SQL code editor, a visual Query Builder, and other helpful tools available in dbForge Studio for SQL Server. They can make you more productive like a pro. So, why not give it a free try by downloading it today? 

Last modified: October 11, 2023