How to Practice SQL JOINs

There is no better way to learn how to practice SQL joins than through hands-on experience. However, there will probably be obstacles along the way. I’ll cover some of these obstacles and give ideas for how to overcome them. While this article won’t turn you into SQL JOINs expert, it will show you how to become one. So, let’s start!

Diving into the world of databases? Then you've likely come across SQL joins. They're the glue that binds data tables together, allowing us to retrieve and analyze information from multiple sources at once. How to practice SQL joins isn't just about memorizing commands; it's about understanding the relationships between data and knowing how to efficiently find what you need.

But, as with many things, the real learning starts when you roll up your sleeves and do the work yourself. While there's a lot of theory behind SQL, it's the actual SQL practice that makes things click. If you don't know what SQL or JOINs are, start with our SQL Basics course and then come back here for more practice ideas.

Before we delve into the nitty-gritty, let's first address some common hurdles and how to tackle them.

Challenges When Practicing SQL JOINs

It’s hard to answer “How do you master SQL JOINs?” However, it’s easy to answer “How do you practice SQL JOINs?” This is the path to mastering SQL JOINs.

If you’re reading this article, you’ve probably already started learning about databases. Also, you probably understand some basic concepts like tables, primary and foreign keys, writing simple SELECT statements, etc.

So, you’re ready to start writing more complex queries but probably face certain challenges. The challenges I address in this article are ones I’ve experienced when learning anything new. So, let’s list the most common challenges.

Using the Right Data

It’s not breaking news to say that databases are focused on data. It starts with real-world data that’s stored digitally in a database. Then, this information needs to be retrieved from the database and turned into real-world reports. I can’t imagine creating meaningful reports from databases without using JOINs.

Maybe you work at a financial institution, maybe you analyze election results, or maybe you wonder how users of your mobile game are actually using it. You might also be preparing for an interview. No matter why, you’ll need to know how to deal with thousands or millions of records in many different tables.

Your goal will be to write queries that return exactly what you want. In order to do that, you’ll need to know how to write the SQL code and at least have an idea of what the final result should be.

While practicing SQL JOINs, a big challenge can be finding an appropriate data set. Companies probably won’t share their data for you to practice with, but practicing with inadequate datasets is not a good option. If the query returns nothing, you can’t be sure if you wrote the query incorrectly or if the dataset doesn’t have anything that fits the criteria.

The ideal dataset will allow you to get results for the most common queries and also for some interesting special cases. Technically, you could create a dataset yourself, but there are some significant challenges:

1. You will need to know how to create it — how to take care of the data, keys, inserts, etc.
2. You will need to know which cases you want to cover with the data. This is hard because you’re practicing to learn this and not the other way around.
3. The dataset will need to be large enough—it can’t be just a few rows in each table. It takes time to gather real-world information (e.g. lists of cities and countries) or produce your own data (imaginary employees, roles, etc.).

Finding the Right Examples

Not only do you need a good dataset, but you also need examples that help you learn effectively. You can easily find thousands of examples on the internet, but some might be too simple while others might be beyond what you can understand at that moment. It’s best to go step-by-step from simple examples to complex ones.

These examples should also be easily understandable and close to problems and implementations you’ll encounter in your job(s). The tables should be ones used in almost any database (e.g. users, customers, students, cars, etc.). This will make the examples more understandable and applicable to real-world situations.

Balancing Theory and Application

Most people learning how to write SQL JOINs are not very interested in the theory behind them. While it’s always good to understand the theory, the situation doesn’t always allow us to spend so much time on it. This applies to almost anything in life, and the key is finding a balance.

You should learn just enough theory so that you understand what you’re doing when you’re writing queries. This doesn’t mean you shouldn’t learn as much as possible – but you should learn it in steps. You should know what's required of you, which JOIN to use, and why.

Practice SQL JOINs Online

No matter what you are learning, you should start with the basics and improve your knowledge over time—not too fast, not too slow. So, what is the best way to learn SQL JOINs?

I personally love history. It is one of my passions (besides databases, of course). My partner often says I’m a “fountain” of bizarre history facts. And I agree with that. Most of these bizarre facts I find through videos. There are great videos available on the internet. I wish I had this when I went to school.

However, there is a problem with this approach when learning SQL JOINs and most other IT-related subjects. You can’t simply just watch a video of somebody else coding and expect to have that knowledge. While you’ll definitely learn something, nothing is better than writing code yourself.

When you watch videos, you take things for granted; you don’t learn through doing. When you start writing the code, you realize that you’re truly learning how to use SQL JOINs. I’m not saying that video courses are not a great part of the learning process. I just believe that you can’t master SQL JOINs by only watching videos.

Therefore, I strongly suggest that you learn SQL JOINs by writing code. One way to do this is to practice online.

I’ll share one of my personal experiences. I have always been thrilled to install a new development environment. After each installation, I would start the program, and, of course, I'd see a blank screen. Okay, that’s expected. So, the next step was usually googling for examples online, but this wasn’t ideal.

While you might be a fan of books, videos, or even googling, I think there is no better way to figure things out than to jump into a course about what you’re having an issue with. You’ll get examples that are properly prepared and described. Moreover, these examples will be ordered in a way so that you can slowly progress to more complicated problems.

With that in mind, I’ve prepared this review of SQL join types and given you some examples of how to use them.

SQL JOIN Types and Examples

SQL supports different types of JOINs in order to return the desired result. Here are the most well-known JOINs.

INNER JOIN

INNER JOIN is used to join two tables based on matching column values. Tables are joined using a condition written after ON. Primary and foreign key attributes are usually used here, but variations are possible.

When you use INNER JOIN, only those rows that have a match in either table will be returned. Here’s what an INNER JOIN looks like:

SELECT *
FROM student
INNER JOIN room ON student.room_id = room.id;

LEFT JOIN

LEFT JOIN does the same thing as INNER JOIN except that it returns all rows from the “left” table, even those that don’t have a match in the “right” table.

SELECT *
FROM student
LEFT JOIN room ON student.room_id = room.id;

RIGHT JOIN

RIGHT JOIN does the same thing as the LEFT JOIN except that it returns all the rows from the “right” table and only the matching rows from the “left” table. Since LEFT JOIN and RIGHT JOIN are similar and you can simulate RIGHT JOIN with LEFT JOIN (just switch the order of tables in the join), RIGHT JOIN is rarely used. Another reason for this is that we read from top to bottom and from left to right. Therefore, the readability of the query using LEFT JOIN is more intuitive.

SELECT *
FROM student
RIGHT JOIN room ON student.room_id = room.id;

Multiple JOINs

Multiple JOINs allow you to join more than two tables. Joining more than two tables is pretty common. You’ll use the same JOINs we’ve mentioned so far, as well as some rules applied to them. Take care that each JOIN has its own conditions written after ON. In order to increase the readability and simplify things, put each JOIN on a new line.

SELECT *
FROM student
INNER JOIN room ON student.room_id = room.id
INNER JOIN equipment ON equipment.room_id = room.id;

FULL JOIN

FULL JOIN returns all rows from the “left” and “right” table, even if they don’t have a match. The syntax follows that of the other JOIN types we’ve already covered:

SELECT *
FROM student
FULL JOIN room ON student.room_id = room.id;

Some DBMSs support FULL JOIN, while others don’t. You can easily simulate FULL JOIN using the combination of LEFT JOIN and RIGHT JOIN, as shown below.

SELECT movie.*, director.*
FROM movie
LEFT JOIN director ON movie.director_id = director.id
UNION
SELECT movie.*, director.*
FROM movie
RIGHT JOIN director ON movie.director_id = director.id;

Self JOINs

Self JOINs are not a type of JOIN – they’re a way to use the same table more than once in a JOIN. This means that you’ll join the table with itself.

When would you need to do this? If you’re told to “list all employees older than this employee” and “find out if there are any directors with lower pay than any of the employees.” To put it simply, you need data from the same table twice, and you need to join/compare these two sets of data based on a condition.

Non-Equi JOINs

So far, we’ve used only the ‘equals’ operator in JOIN conditions (after the keyword ON). However, that’s not required. You can do whatever you want in a condition, but be aware that it will impact the output (and, of course, be sure you know what you’re doing). Here’s an example of a non-equi join:

SELECT *
FROM student s1
INNER JOIN student s2 on s1.id < s2.id
ORDER BY s1.id, s2.id;

Notice that the above query is an example of a self-join (we’ve used the student table twice), as well as a non-equi join (using “<” instead of “=” in the condition s1.id < s2.id). The query itself returns all students combined with other students (but them). Each combination is returned exactly once.

Do you want to know more about SQL JOINs? Watch this episode of our We Learn SQL series on YouTube:

Time to Practice SQL JOINs!

The SQL JOINs course on LearnSQL.com delves deep into one of SQL's most fundamental features. Designed for those with basic SQL experience, this course comprehensively covers all types of SQL JOINs, equipping learners with the skills to query databases effectively. By the end of the course, participants will be adept at selecting data across multiple tables – a crucial skill for any SQL user.

If you would like to watch some videos as part of your learning process, I recommend this one. I think you’ll enjoy it, especially if you’re a fan of SQL JOINs and science fiction.

Simply create a free account (or log in if you already have one) and begin this engaging, interactive course. The best part? You don't need to install a thing. Everything operates seamlessly within your preferred browser.

There is no easy answer to the question ”How do you master SQL JOINs?” The only way you can do it is by trying, failing, and finally succeeding. How long it will take depends on your current knowledge and how you practice. That brings us again to the question “How do you practice SQL JOINs?” In the end, this is the question we need to answer.

Whatever path you choose, be sure you get the most from investing your time and money. While money can be earned, nobody will give you back your time. My suggestion is to practice with examples as much as possible.