SQL JOIN Tips for Beginners
If you’re a beginner and want to know how to start using SQL JOINs in your queries, you’ve come to the right place.
Using the SQL JOIN
clause is necessary if you want to query multiple tables. Sooner or later, you’ll have to use more than one table in a query. It’s the nature of relational databases in general – they consist of data that’s usually saved in multiple tables; in turn, these form a database. Once you get familiar with basic SQL queries, it’s wise to start learning the JOIN
clause. Besides the tips from this article, our SQL JOINs course can help you with this.
In this article, I’ll walk you through four tips for using the JOIN
clause. But first, let me show you the tables we’ll be working with.
Get to Know the Tables
In helping you with JOINs
, I’ll use three tables:
manufacturer
product_type
product
The table manufacturer
contains the following attributes:
The table manufacturer contains the following attributes:
- id – The manufacturer’s ID number in the database.
- manufacturer_name – The name of the manufacturer.
The second table is product_type, which has these attributes:
- id – The ID of the product type.
- type_name – The name of the product type.
The last table is the product table, which has four attributes:
- id – The ID of the product.
- product_name – The name of the product.
- type – The type of the product; references the table
- manufactured_by – The name of the product manufacturer; references the table
It’s time for the first tip!
Tip #1: Use Explicit JOIN Syntax
You’re probably wondering what that even means! Don’t worry; it’ll be clear in a few moments.
As you know, the JOIN
clause is used to combine or join data from two or more tables. However, this is not the only way to join tables. There is also an old syntax – from before SQL-92 – in which tables are joined by listing them in the FROM
clause. This syntax is used by a lot of beginners, which is fine and will do exactly the same job as an explicit JOIN syntax. But learning the explicit JOIN syntax pays off in the long run. It has several benefits over listing tables in the FROM
clause, which I will talk about later in this article.
I’ll show you how non-explicit table joins work. Let’s say you want to get a list of products together with their manufacturer names. Here’s how to do it by listing two tables in the FROM
clause:
SELECT product.id, product.product_name, manufacturer.manufacturer_name FROM product, manufacturer WHERE product.manufactured_by = manufacturer.id; |
I’ve first listed the columns I want in the result. They are id
and product_name
from the table product
and manufacturer_name
from the table manufacturer
. Then I’ve listed both the product
and the manufacturer
table in the FROM
clause. Finally, I’ve stated that the column manufactured_by
from the table product
should be equal to the column id
from the table manufacturer
.
The result?
id | product_name | manufacturer_name |
---|---|---|
1 | SuperFX | QuazySoftware |
2 | UnterB | DieBeste |
3 | HyperXYZ | FabricaInfinita |
4 | TurboRay | QuazySoftware |
5 | VeryTrade | DieBeste |
6 | BlueSky | FabricaInfinita |
7 | DirtyGritty | QuazySoftware |
8 | SmoothUndertaker | DieBeste |
9 | HelioTroll | FabricaInfinita |
You can get the same result by using the JOIN
statement:
SELECT product.id, product.product_name, manufacturer.manufacturer_name FROM product JOIN manufacturer ON product.manufactured_by = manufacturer.id; |
The SELECT
part is the same in both queries. But instead of simply listing two tables in the FROM
clause, I’ve joined them by explicitly writing the JOIN
clause. If you write this clause, it’s necessary to specify which columns will be used when connecting the two tables. You do that by using the ON
clause and connecting the column manufactured_by
from the table product
with the column id
from the table manufacturer
. If you look closer, you’ll notice the ON
clause works like the WHERE
clause in the previous query.
Why do I recommend using an explicit JOIN
statement? First, this way lets you easily see which type of join is used to combine the tables. There are four types of joins (inner join, left join, right join, and full join), which I’ll discuss later on. When the type of join is explicitly stated in the query, it’s easier to understand what the query does.
The other reason to use the explicit JOIN
syntax is to see the difference between the join conditions and filtering conditions. Since the WHERE
clause is used for filtering data, it can be confusing to use it to join tables.
There are more advantages of using explicit JOINs that I don’t have room to cover; see this article, which talks about the advantages of SQL JOINs, for more details.
If you need to recap your basic SQL knowledge before moving on, our SQL Basics course could be ideal for that. You can also use its JOIN section to start learning how to join tables.
Tip #2: Use Table Aliases
To show you what I mean by the aliases and why they are useful, let me show you how a query looks without them. If I wanted to list all the products and their product type names, here’s one way it can be done:
SELECT product.id, product.product_name, product_type.type_name FROM product JOIN product_type ON product.type = product_type.id; |
First, the query explanation. Like the previous example, this query selects the columns id
and product_name
from the table product
. It also selects the column type_name
from the table product_type
. The tables product
and product_type
are joined on the column type from the table product
and the column id
from the table product_type
.
Here’s the result:
id | product_name | type_name |
---|---|---|
1 | SuperFX | Server |
2 | UnterB | Server |
3 | HyperXYZ | Server |
4 | TurboRay | Laptop |
5 | VeryTrade | Laptop |
6 | BlueSky | Laptop |
7 | DirtyGritty | Software |
8 | SmoothUndertaker | Software |
9 | HelioTroll | Software |
Even though this query is not complicated, writing it is cumbersome. Every time I want to reference the table, I have to write its full name. If that’s not enough, there’s also a table whose name consists of two words and an underscore. Imagine if there are more tables and with longer names! However, there’s a way to make your life easier. Instead of writing tables’ full names, you can assign each one an alias:
SELECT p.id, p.product_name, pt.type_name FROM product p JOIN product_type pt ON p.type = pt.id; |
This is precisely the same query as above, but it uses table aliases instead of full names. Take a look! I’ve assigned the aliases in the FROM and JOIN
clauses. You’ll notice the difference; the table product
is now called p
, and the table product_type
is now called pt
. Of course, with the tables’ names changed, these new names have to be used when selecting the columns id
, product_name
, and type_name
.
I think the query now looks much better. And it sure was more comfortable to write it! One more piece of advice; use meaningful aliases! The usual practice is to use the first one or two letters of the table name or the first letter of every word in the table name. The point is that your alias should be as short as possible, but it should also feel like a “natural” choice for an alias; you should be able to know immediately which alias refers to which table.
Tip #3: Know the Difference Between Different JOIN Types
I’ve mentioned before that there are four basic JOIN
types:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
The INNER JOIN
clause will return only matching records that are the same in both tables. Note that INNER JOIN
is the same as JOIN
.
The LEFT JOIN
returns all the records from the left table (the first table, placed after FROM
) and only matching records from the right table (the second table, placed after JOIN
).
On the other hand, the RIGHT JOIN
returns all the records from the right table and only matching records from the left table.
Finally, FULL OUTER JOIN
returns all the records from both tables. Any non-matching records are filled with NULL
values. This join potentially returns a huge amount of data. Note that FULL OUTER JOIN
is the same as FULL JOIN
.
A more detailed but beginner-friendly explanation can be found in this article about SQL JOIN types.
I know it can be hard to remember everything, especially if you’re a beginner or you don’t use joins very often. But there’s no need to remember everything because there’s the SQL JOIN cheat sheet. You can save it or print it and use it whenever you use the JOIN
clause or want to learn something new at a glance.
In practice, you’ll most often use INNER JOIN
and LEFT JOI
N, so let’s concentrate on those two. You’ve seen how to use INNER JOIN
in the previous examples. Let me now show you how LEFT JOIN
works.
In this example, we’ll list all the product types and products belonging to each category. You could do it this way:
SELECT pt.id, pt.type_name, p.product_name FROM product_type pt LEFT JOIN product p ON pt.id = p.type; |
This query first selects the desired columns; nothing new here. Then it joins the two tables and assigns them aliases. I’ve joined the table using something new; the LEFT JOIN
statement. Just to remind you, this join selects all the rows from the left table (product_type
) and the matching rows from the right table (product
). This is exactly what I needed; take a look:
id | type_name | product_name |
---|---|---|
1 | Software | DirtyGritty |
1 | Software | SmoothUndertaker |
1 | Software | HelioTroll |
2 | Laptop | TurboRay |
2 | Laptop | VeryTrade |
2 | Laptop | BlueSky |
3 | Server | SuperFX |
3 | Server | UnterB |
3 | Server | HyperXYZ |
4 | Scooter | NULL |
5 | Printer | NULL |
Note there are no products for the categories “Scooter” and “Printer”. In cases when there are no matching rows in the right table, the result will be NULL
.
Tip #4: Map Out Multiple Joins
You’re not limited to joining only two tables; you can join three or more tables. Let me first show you how to do it, and then I’ll give you a few helpful tips.
Your task is to list all the products together with their product type and manufacturer name. This code will do that:
SELECT p.id, p.product_name, pt.type_name, m.manufacturer_name FROM product p JOIN product_type pt ON p.type = pt.id JOIN manufacturer m ON p.manufactured_by = m.id; |
As in the previous examples, this code first selects the columns that will be shown in the result. The table product
is joined with the table product_type
; you’ve already practiced that. OK, but now you need data from the table manufacturer
; how do you join it? You simply write another JOIN
clause and specify the desired table.
However, even though the join comes after the table product_type
, it doesn’t mean the table manufacturer
is joined with this table, at least in this case. No, it’s joined with the table product
. This is obvious from the columns I’ve specified; the tables are connected via the columns manufactured_by
from the table product
and id
from manufacturer
.
Here’s the result:
id | product_name | type_name | manufacturer_name |
---|---|---|---|
1 | SuperFX | Server | QuazySoftware |
2 | UnterB | Server | DieBeste |
3 | HyperXYZ | Server | FabricaInfinita |
4 | TurboRay | Laptop | QuazySoftware |
5 | VeryTrade | Laptop | DieBeste |
6 | BlueSky | Laptop | FabricaInfinita |
7 | DirtyGritty | Software | QuazySoftware |
8 | SmoothUndertaker | Software | DieBeste |
9 | HelioTroll | Software | FabricaInfinita |
When you join multiple tables, it’s a good idea to map out the tables you want to join. Make a little drawing; it’s easier to visualize the joins that way. This is especially handy if you’re doing a left or right join. You won’t have to imagine it; the table you draw on the left side is your left table and LEFT JOIN
will give you all the data from it. The table you draw on the right is the right table. It seems obvious, but it’s harder to imagine it without drawing, particularly if you’re a beginner. The drawing could be something as simple as this:

Another tip is to list the columns in the SELECT statement in the same order as you join the tables. If you look at the above query, the columns are from the tables product
, product_type
, and manufacturer
, respectively. The tables are joined in precisely the same order. Doing this will make your code more readable and easier to change.
Last but not least, always join tables in a sort of chain. In other words, every table you join should be joined to the table(s) that are already joined in your query. If you join two tables and then decide to join another one, the third table can’t be left “hanging loose”, unattached to the previous tables. Make sure the ON
conditions link the new table to the previously-joined tables. Here’s how NOT to do it:

You’ve learned quite a lot. This is not everything you need to know about joining three tables, of course. There are details I didn’t mention that are nicely explained in this article about multi-table joins. And now that you know about LEFT JOIN and joining multiple tables, why not learn how to LEFT JOIN multiple tables?
Was Learning This EnJOINable?
With these tips on using joins in SQL, I’ve tried to make your first steps as easy as possible. You’ve learned how to join two tables using both explicit JOIN
syntax and listing tables in the FROM
clause. Along with that, you’ve learned to use aliases and what different joins do. Finally, I’ve shown you how to join three tables. I think this is a massive step for you in quite a short time. Congratulations!
So, how can you put all these tips into practice? This article on practicing SQL JOINs will help you find the approach most suitable for you. Or you can practice using our SQL Basics or SQL JOINs course. It depends on how detailed you want your knowledge to be.
Care to share your experience with joining tables? The comments section awaits you!