How to Use SQL JOIN with Multiple Tables: The Basics of Joining in SQL

Sometimes you may be inclined to combine data from different columns over multiple tables. You can do this using a JOIN. JOIN is used to combine columns from two or more tables. Tables are joined two at a time, making a new virtual (in memory) table containing all the relevant row combinations from the original two data sets. Because there are multiple types of JOIN commands, it’s a good idea to understand all of them. That way, you’ll never be confused about which type of JOIN to use when you create your own statements.

As a developer, you may have different approaches to handling database queries. In this tutorial, we’ll elaborate on how to join datasets with some of the more frequently used methods, including consolidating multiple datasets in a single SELECT statement. Practice and experience will make it easier to know when to use which type of query.

If you are new to writing SQL queries or need a refresher, you might want to start with our list of useful SQL commands. It will give you an overview of using operations like INSERT INTO, DELETE, and ORDER BY. 

Different types of SQL JOINs

Types of SQL JOINS

Following are the different types of SQL join operations:

• SQL INNER JOIN (or sometimes called simple join)
• SQL LEFT OUTER JOIN (usually called LEFT JOIN)
• SQL RIGHT OUTER JOIN (usually called RIGHT JOIN)
• SQL FULL OUTER JOIN (usually called FULL JOIN)
• SQL CROSS JOIN

Why do there need to be so many types of JOINs?

These will all join multiple tables. But they will relate to the tables in SQL differently; with these SQL JOINs and the correct JOIN conditions, you can have in-depth control over how the data is identified and returned.

Understanding the above JOINs is a lot like having a very good toolbox. All of these JOINs are best for different scenarios, and none of them is an “all-in-one” tool. When differentiating between them, you’ll need to consider how you want the data compared and what data you want to return to you.

For many SQL developers who are just learning, the best way to really understand these JOINs is to create two (or more) tables and run these statements on them. By running these statements and seeing the results, you’ll learn much faster how they work.

In the below examples, we will be looking at two tables, table1 and table2. Table1 has rows with IDs between 3 and 19, and Table2 has IDs between 16 and 29. Therefore, there is an overlap, as both tables have IDs 16, 17, 18 and 19. The below Venn diagram represents this.

In this diagram, IDs 3 to 19 are shown in the circle above the word table1, and IDs 16 to 29 are shown in the circle above table2. There is an overlap between the two tables, which shows IDs 16 to 19.

When we look at the different JOINs, we will see the above Venn diagram, and the results retrieved by the JOIN will be in a lighter color.

SQL INNER JOIN

Definition: This returns all rows from multiple tables where the join condition is met or returns the records where table1 and table2 intersect. An INNER JOIN will only return rows for which there is data in both of the tables. It specifies the complete cross-product of two tables.

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

Let’s break down the above query. First of all, the SELECT clause shows which columns you need to retrieve. If it is unclear which table a column comes from, you need to prefix it with the table name followed by a dot.

Then there is the FROM clause, where you are joining the tables. It shows the first dataset, which could be a table or a previously saved query. Then you will have the JOIN clause, which starts with the type of JOIN – in this case, it is an INNER JOIN. Then we specify the second dataset. The table order is not important for an INNER JOIN, but it is important for LEFT and RIGHT OUTER JOINs. 

It is followed by the word ON and shows the join condition. These are generally the columns where the data is the same, and the columns are separated by an equal sign. Quite often, this will join a primary key in one table to a foreign key in another table.

An alternative way of expressing this query is:

SELECT columns

FROM table1, table2

WHERE table1.column = table2.column;

Note that the ON word has been changed to a WHERE clause and that a comma has replaced the words “INNER JOIN”. This is an older syntax and is rarely used nowadays. However, it is useful to understand what this does if you encounter any historical queries which use it.

An INNER JOIN is very similar to the OUTER JOINs, which are shown below. Still, you need to know what you’re going to do beforehand — whether you need to return all records in one or both tables (where an OUTER JOIN would be more appropriate) or just want only the records that match in both tables (an INNER JOIN). An OUTER JOIN provides you with more rows, but an INNER JOIN will return everything that matches and nothing more.

INNER JOINs, however, might also provide information that doesn’t apply to your needs. Because they will only include rows that fulfill the JOIN conditions, they exclude all rows which do not match. It is the default join, and so if you replace “INNER JOIN” with “JOIN,” then, in most SQL variants, the query will run the same.

The results which are retrieved are visualized in the below Venn diagram. Because IDs 16 to 19 are the only IDs common to both tables, they are the only ones retrieved by an INNER JOIN and result in a dataset of 4 rows. They are shown as the intersection between the two circles and are shown in a lighter color. 

SQL LEFT OUTER JOIN

Definition: This type of join returns all rows from the LEFT-hand table and only those rows from the other table where the join condition is met. The LEFT table is the table shown before the JOIN. The below SELECT statement returns all records from table1 (as this is the table before the word “JOIN”) and only those records from table2 that intersect with table1. Any rows from table2 which are not matched from table1 are not included in the results.

SELECT columns

FROM table1

LEFT OUTER JOIN table2

ON table1.column = table2.column;

The words “LEFT OUTER JOIN” in the above query are commonly simplified to “LEFT JOIN.” You should ensure that you are correctly using a SQL LEFT OUTER JOIN, as it will retrieve all of the rows in the left-hand data set by default. If you want to retrieve all of the rows in the right-hand data set, you should use a SQL RIGHT OUTER JOIN instead. You should also ensure that you use the word “LEFT”. You will get a FULL OUTER JOIN or an INNER JOIN instead if you don’t.

If you use an INNER JOIN and find that you aren’t getting the data they would need, you should consider using an OUTER JOIN. A deeper understanding of LEFT and RIGHT joins can make finding and manipulating data much easier. 

Remember: the terms LEFT and RIGHT are relative. They reflect the order in which you list your tables in the SELECT statement.

In the below Venn diagram, all of table1 is retrieved, together with any columns from table2 which are in IDs 16, 17, 18, 19, resulting in a dataset of 10 rows. For IDs 3 to 9, as there is no relevant data in table2, it will retrieve NULLs for those columns.

SQL RIGHT OUTER JOIN

Description: This type of join returns all rows from the RIGHT-hand table and only those rows from the other table where the join condition is met. The RIGHT-hand table is the table shown after the word “JOIN”. The below SELECT statement returns all records from table2 and only those records from table1 that intersect with table2.

SELECT columns

FROM table1

RIGHT OUTER JOIN table2

ON table1.column = table2.column;

Just like with a LEFT JOIN, the words “RIGHT OUTER JOIN” in the above query are commonly simplified to “RIGHT JOIN.” Of course, you also control the order of the tables in the SELECT statement that you bring in. Therefore, this query will execute identically to this query:

SELECT columns

FROM table2

LEFT JOIN table1

ON table1.column = table2.column;

Consider that when you refer to “FROM CUSTOMERS RIGHT JOIN ORDERS,” the CUSTOMERS table is the LEFT-hand table, and the ORDERS table is the RIGHT-hand table. However, you could just as easily refer to “FROM ORDERS LEFT JOIN CUSTOMERS.” So, LEFT and RIGHT JOINs do help you reduce the amount that you have to change the order of tables in a query.

Because of this, in practice, you generally use a RIGHT OUTER JOIN when you have already created the query but want to retrieve a different set of results. However, there may be times when you want to be consistent regarding which tables are always “LEFT” and which tables are always “RIGHT” — usually if you’re going to conceptualize some tables (like the “customers” table) as being the main table with the significant data storage.

In the below Venn diagram, the retrieved data is the lightly shaded circle showing all the rows from table2, including the intersection between table1 and table2. This results in a dataset of 13 rows. As table1 does not include IDs 20 to 29, any columns from table1 for those IDs will be NULL.

SQL FULL OUTER JOIN

Definition: This type of join returns all rows from the LEFT-hand table and RIGHT-hand table. Where the join condition is not met, NULLs are returned. The result after the JOIN operation will be all the records from both table1 and table2.

SELECT columns

FROM table1

FULL OUTER JOIN table2

ON table1.column = table2.column;

Some databases replace the FULL OUTER JOIN keywords with FULL JOIN, so you should make sure to check on the syntax that your database uses.

In the below Venn diagram, both of the circles are lightly shaded, as they will all be retrieved, resulting in a dataset of 19 rows. Any columns from table2 for IDs 3-9 and any columns from table1 for IDs 20-29 will be shown as NULL.

SQL CROSS JOIN

Definition: This will return all records where each row from the first table is combined with each row from the second table. You can specify a CROSS JOIN in two ways: using the JOIN syntax or listing the tables in the FROM clause separated by commas without using a WHERE clause to supply the join criteria.

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

Please note that there is no “ON” or join conditions in this SELECT statement. An older way to express this query is:

SELECT * FROM [TABLE 1], [TABLE 2]

A CROSS JOIN is a pretty advanced type of JOIN, but the syntax is actually pretty simple; it just creates a combination of the first table’s row and the second table’s row. It’s also known as a Cartesian Join. But because it can seem complex, it’s good to pay special attention to your syntax. 

In the below Venn diagram, you will see that the two circles are now separated. This is because there are no JOIN conditions. In the results, the row of ID 3 in table1 is then shown with all of the rows in table2. Then, the row ID 5 in table1 is demonstrated with all of the rows in table2. This results in 130 rows, which is significantly more than any of the other JOINs that we have seen in this article.

While a CROSS JOIN is powerful because it results in a multiplication of rows, you may never use it. Using INNER and LEFT JOINs is much more common. When used appropriately, it can provide some powerful results.

JOINs in Oracle SQL

Throughout this article, we have been using the standard form of creating JOINs, which uses the word “ON” followed by a join operator.

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

This type of code is very portable and can be used in most SQL variants.

In Oracle SQL, there are ways to reduce the amount of typing needed. The first way is to change the “ON” to a “USING”, and put the column name in brackets. In this way, you only have to specify the join columns once.

SELECT columns

FROM table1

INNER JOIN table2

USING (column);

The second way in Oracle SQL is to let Oracle choose the columns. It will look at the two tables and create the join based on columns that have the same name in both tables. You put the word “NATURAL” before the start of the join, and you remove the ON or USING section:

SELECT columns

FROM table1

NATURAL INNER JOIN table2;

In Oracle SQL, all of these queries are identical. However, the last two queries will not be portable to other variants of SQL.

Combining the rows of different tables

The JOINs shown in the article compare rows in one table with another. However, you might want to add rows from multiple tables or datasets. The UNION operator is another way to return information from multiple tables with a single query. It allows you to perform queries against several tables and return the results in a consolidated set, as in the following example:

SELECT column1, column2, column3 FROM table1

UNION 

SELECT column1, column2, column3 FROM table2;

This will return a result set with three columns containing data from both queries. The UNION operator will omit duplicates between the tables, where the content in all of a row is the same as another row. If you want to retain duplicates, you should use the UNION ALL operator. 

Looking at the data in the Venn diagram, there are 10 rows in table1 and 13 rows in table2. If you use the following query:

SELECT ID FROM table1

UNION 

SELECT ID FROM table2;

This will retrieve 19 rows, removing the duplicate IDs 16, 17, 18 and 19. If you were to use UNION ALL instead, then those duplicates will be retained, and it will retrieve all 23 rows.

UNION is helpful when the returned columns from the different tables have the same sort of data, just in different time periods. For example, you may have a tblToday, tblYesterday, tblLastMonth and tblArchive, which contain the same data types. This means that you can access only today’s data without needing a WHERE clause, but you could UNION those tables together when necessary. UNION statements can create a heavy load on your database server, as processing is needed to remove the duplicates, but they can be very useful.

Suppose you find yourself needing to do a lot of UNION queries. In that case, you may wish to consider whether you should restructure your database so that these tables are encompassed within a single table or at least that some data may need to be shifted. However, it can be very useful when accessing some data more often than other data.

Summary

Your success when using SQL databases lies in structuring and developing a properly designed database, which will help you retrieve information from as many tables as you want, specify retrieval conditions based on any data in the tables, and show the results in any order that you like. 

JOIN commands may initially seem complex, but they’re not as complicated as it may seem. The primary issue with JOIN commands is that they often aren’t easily human-readable. They may be very lengthy. Usually, you need to be very specific about the columns and values you’re selecting, which leads to queries that look confusing at first glance.

However, once you strip out the more complex parts of the JOIN command, they’re very straightforward. They’re just a way of comparing data sets. 

Understanding the different types of SQL JOIN in-depth will help you write better SQL SELECT statements. Knowing all the tools at your disposal means that you will also understand innately when another tool is best for the job. At the same time, that doesn’t mean you should always use the most complex format. JOIN queries do come at a price; the more complex the JOIN query is, the more likely it is to be resource-intensive as well.

When creating subqueries, always remember that performance is an important factor. Each query is processed separately in full before being used as a resource for your primary query. Creative use of JOIN statements may provide better results with a lesser lag time. But if you find that you’re experiencing high resource usage and need to use many JOIN statements to get the results you need, you may want to check whether your database should be structured differently.

If you would like to know about writing SQL queries, please look at my courses. For help with SQL Server, then please look at my 29-hour course “Querying Microsoft SQL Server with Transact-SQL.” If you want a shorter course, please look at my 8-hour “Database Fundamentals” course or my “SQL Server Essentials in an hour: The SELECT statement.” If you would like to learn more about Oracle SQL, please look at my “1Z0-071 Oracle SQL Developer: Certified Associate (Database)”.

Thank you very much for reading this, and keep learning.