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:

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?

You can get the same result by using the JOIN statement:

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:

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:

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:

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 JOIN, 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:

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:

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:

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:

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!