How to Use SQL JOIN with Multiple Tables: The Basics of Joining in SQL
Sometimes you may be inclined to combine data that spans over multiple tables. During these times, you might wonder what SQL syntax is best. The answer, of course, is JOIN. But it’s a more complex answer than just that.
JOIN is a syntax often used to combine and consolidate two or more tables. Tables are joined two at a time, making a new table that contains all possible combinations of rows from the original two tables — or with multiple tables at once. There are numerous types of JOIN commands, depending on the tables that need to combine and the data inside of them. 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’re ready to start creating your own statements.
As a developer, you may have different approaches towards handling database queries. In this tutorial, we’ll elaborate on how to join tables with some of the more frequently used methods — including how to consolidate queries on multiple tables into a single statement. While some of these options may affect performance, increase or decrease the processing time, or pop-up errors, 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 how to use operations like INSERT INTO, DELETE, and ORDERBY.
The simplest method to join multiple tables
First, let’s start with the easiest method you can use to join multiple tables. It’s so simple that you may have already been doing it.
The fastest, simplest way to query multiple tables is directly through the SELECT statement. You can call more than one table by using the FROM clause to combine results from multiple tables.
SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;
Most people will join tables without even realizing it every day, using the SELECT statement. The advantage to the SELECT statement is that it’s very simple; as noted, most people don’t even realize that they’ve used it. But the disadvantage to the SELECT statement is that it doesn’t afford you a lot of control regarding how the table is being joined.
And, of course, it can feel messy as well. Because every column has to be called with its table name and its own name, it can be difficult to read.
Let’s say you want to control the table’s order when you select data from multiple tables, connect to the left table specifically, or use foreign keys. Other types of joins and SQL queries might be more applicable, and you may need advanced join operations.
Other JOIN SQL queries will give you more control over data manipulation. This can help you not only get the results that you need but also control the resources that you use when completing the query. Joining multiple tables is often resource-intensive, both in terms of CPU and memory. Because of this, you want to make sure that you are streamlining and optimizing your queries as much as possible.
The UNION statement is another way to return information from multiple tables with a single query. The UNION statement 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. By default, the UNION statement will omit duplicates between the tables unless the UNION ALL keyword is used.
UNION is helpful when the returned columns from the different tables don’t have columns or data that can be compared and joined, or when it prevents running multiple queries and appending the results in your application code. As with subqueries, UNION statements can create a heavy load on your database server, but for occasional use, they can save a lot of time.
If you find yourself needing to do a lot of UNION queries, it’s more or less likely that you’ll have to restructure your database so that these tables are encompassed within a single table, or at least that some data may need to be shifted. UNION queries shouldn’t be frequent or built into an application because of their heavy resource load; they are not as scalable as many other queries because of the amount of data manipulation they need to do.
Performing a SQL join command
With SELECT and UNION, some databases may have a limit on the number of tables that they can handle. That’s when it is a good idea to use the SQL JOIN statement to join two or more tables. Joining the tables will likely have more strain on the SQL server, but that also depends on the type of SQL JOIN statement used and the JOIN clause.
In reality, there are multiple types of SQL statements. There are SQL statements used one-off and on-the-fly by developers who are querying their database manually. Some programmers even incorporate SQL commands in their applications. When adding SQL statements into applications, they must be scalable. A SQL join is a powerful tool, but consider how it will adapt as the database grows and as the application’s usage increases.
Let’s take a look at all the types of SQL joins.
Types of SQL JOINS
Following are the different types of SQL joins:
- SQL INNER JOIN (or sometimes called simple join)
- SQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
- SQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
- SQL FULL OUTER JOIN (or sometimes called FULL JOIN)
- SQL CROSS JOIN
Why do there need to be so many JOIN commands?
These will all join multiple tables. But they will relate to the tables in SQL differently; with these SQL JOINs and the right 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 JOIN statements 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 paired, what data you want to return to you, and how significant your resource usage will be.
For many SQL developers who are just learning, the best way to really understand these JOIN statements 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.
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;
An INNER JOIN is very similar to an OUTER JOIN, but you need to know which you’re going to do beforehand — whether you need to return all records or whether you just want the records that are applicable. OUTER JOIN provides you more control over the data, but an INNER JOIN will return pretty much everything you might need.
INNER JOINs, however, might also provide information that doesn’t apply to your needs. Because they will include rows that don’t fulfill all your requirements, they can fill your query with empty results.
SQL LEFT OUTER JOIN
Definition: This type of join returns all rows from the LEFT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. This statement returns all records from table1 and only those records from table2 that intersect with table1.
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;
Many people will use a SQL LEFT OUTER JOIN by default without necessarily considering that they might need a SQL RIGHT OUTER JOIN instead. And still, others will just use an OUTER JOIN or an INNER JOIN without specifying a LEFT or RIGHT table.
In fact, using LEFT or RIGHT tables is usually more advanced, and most people will only do it if they aren’t getting the data that they would need otherwise. Nevertheless, a deeper understanding of LEFT and RIGHT joins can make it much easier to find and manipulate data.
Remember: the terms LEFT and RIGHT are relative. They are going to reflect the order in which you list your tables.
A classic INNER JOIN or OUTER JOIN statement is usually what someone will use if a SELECT or UNION statement hasn’t worked for them. In other words, INNER JOIN and OUTER JOIN statements are the most common types of JOIN, without any LEFT, RIGHT, or OUTER characteristics. So, if you’re going to learn any kind of JOIN statements, you’re going to want to understand these two quite well.
SQL RIGHT OUTER JOIN
Description: This type of join returns all rows from the RIGHT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. In other words, this 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;
Of course, you also control the order of the tables that you bring in. So, you can always bring in Table1 as Table2, and vice versa. Because of this, RIGHT OUTER JOIN is rarely used because you can replace it with LEFT OUTER JOIN with no real loss of functionality.
Consider that when you refer to CUSTOMERS and then ORDERS, the CUSTOMERS table is LEFT, and the ORDERS table is RIGHT. But you could just as easily refer to ORDERS and then CUSTOMERS. So, LEFT and RIGHT do make sense to help you figure out things on the abstract, but in reality, you can order things however you want, and a LEFT JOIN will usually do what you want it to.
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 major data storage.
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 inserted. 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.
All databases like Oracle, Microsoft SQL, and MySQL originate from SQL standards. With that being said, they don’t all follow and implement these SQL standards in the same way. Because of this, some syntax can vary depending on the program that you’re running. You should always check in with your SQL server first.
In addition to documentation, most SQL servers have a “HELP” command that lets you learn more about functions. If you type in a function without any modifiers, you may also get an overview of its syntax and how to use it successfully.
SQL CROSS JOIN
Definition: This will return all records where each row from the first table combines 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] OR SELECT * FROM [TABLE 1], [TABLE 2]
A CROSS JOIN is a pretty advanced type of JOIN, but it’s 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 the syntax you’re using.
While a CROSS JOIN is powerful (and deceptively simple), you may never use it; most people will use some other form of JOIN or connect their data in another way. But when used, it provides the easy consolidation of data from multiple tables.
Your success as a successful DBA lies in structuring and developing a properly designed and linked 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 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 human-readable. They tend to be very lengthy and aren’t “elegant.” 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. If you look at a spreadsheet, for instance, these types of data consolidation become quite intuitive.
While most people tend to use SELECT statements to JOIN tables, understanding other types of SQL JOIN in-depth will make someone a better DBA. 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 (and interesting) the JOIN query might be, the more likely it is to be resource-intensive as well.
Always remember that performance is an important factor for subqueries. Each query is processed separately in full before being used as a resource for your primary query. So creative use of JOIN statements may provide better results with a lesser lag time. But you may also need to structure your database differently if you find that you’re experiencing high resource usage and that you need to use a lot of JOIN statements to get the results you need.
Top courses in SQL
SQL students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.