Esat Erkec

SQL multiple joins for beginners with examples

October 16, 2019 by

In this article, we will learn the SQL multiple joins concept and reinforce our learnings with pretty simple examples, which are explained with illustrations. In relational databases, data is stored in tables. Without a doubt, and most of the time, we need a result set that is formed combining data from several tables. The joins allow us to combine data from two or more tables so that we are able to join data of the tables so that we can easily retrieve data from multiple tables. You might ask yourself how many different types of join exist in SQL Server. The answer is there are four main types of joins that exist in SQL Server. First of all, we will briefly describe them using Venn diagram illustrations:

  • Inner join returns the rows that match in both tables

    SQL inner join venn diagram

  • Left join returns all rows from the left table

    SQL left join venn diagram

  • Right join returns all rows from the right table

    SQL right  join venn diagram

  • Full join returns whole rows from both tables

    SQL full  join venn diagram

If you lack knowledge about the SQL join concept in the SQL Server, you can see the SQL Join types overview and tutorial article.

After this short explanatory about the SQL joins types, we will go through the multiple joins.

What are SQL multiple joins?

Multiple joins can be described as follows; multiple join is a query that contains the same or different join types, which are used more than once. Thus, we gain the ability to combine multiple tables of data in order to overcome relational database issues.

Example scenario

Green-Tree company launched a new campaign for the New Year and made different offers to its online customers. As a result of their campaign, they succeeded in converting some offers to sales. In the following examples, we will uncover the new year campaign data details of the Green-Tree company.

The company stores these campaign data details in the following tables. Now, we will create these tables through the following query and populate them with some dummy data:

How SQL multiple joins work?

Business problem: Which customers were interested in this New Year campaign?

In order to answer this question, we need to find out the matched rows for all the tables because some customers did not receive an email offer, and some offers could not be converted into a sale. The following Venn diagram will help us to figure out the matched rows which we need. In short, the result of this query should be the intersecting rows of all tables in the query. The grey-colored area specifies these rows in the Venn diagram:

SQL multiple joins Venn diagram image

The SQL multiple joins approach will help us to join onlinecustomers, orders, and sales tables. As shown in the Venn diagram, we need to matched rows of all tables. For this reason, we will combine all tables with an inner join clause. The following query will return a result set that is desired from us and will answer the question:

At first, we will analyze the query. An inner join clause that is between onlinecustomers and orders tables derived the matched rows between these two tables. The second inner join clause that combines the sales table derived the matched rows from the previous result set. The following colored tables illustration will help us to understand the joined tables data matching in the query. The yellow-colored rows specify matched data between onlinecustomers and orders. On the other hand, only the blue colored rows exist in the sales tables so the query result will be blue colored rows:

SQL multiple joins working mechanism

The result of the query will look like this:

SQL multiple join query result

Different join types usage in SQL multiple joins

Business problem: Which offers could not be converted into a sell?

We can use the different types of joins in a single query so that we can overcome different relational database issues. In this example, we need all rows of the orders table, which are matched to onlinecustomers tables. On the other hand, these rows do not exist in the sales table. The following Venn diagram will help us to figure out the matched rows which we need. The grey-colored area indicates rows which will be the output of the query:

SQL multiple joins Venn diagram image

In the first step, we should combine the onlinecustomers and orders tables through the inner join clause because inner join returns all the matched rows between onlinecustomers and orders tables. In the second step, we will combine the orders table to the sales table through the left join and then filter the null values because we need to eliminate the rows which are stored by the sales table:

The result of the query will look like this:

Combining multiple tables with different join types result

Quiz

Question: Please generate the proper query according to the below Venn diagram.

Combine more than two tables with full join

Answer: As we learned, the full join allows us to return all rows from the combined tables. The answered query will be like the following:

Multiple full join resultset

Conclusion

In this article, we focused on the SQL multiple joins approach and learned it with detailed examples. Multiple joins allow us to combine more than two tables so that we can overcome different issues in the relational database system. Furthermore, we saw how we could use different join types in a single query.

Esat Erkec
168 Views