How to Practice SQL JOINs

There is no better way to learn SQL joins than through practice. 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!

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 and understand what they are. 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 what, 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 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—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 also examples that help you learn efficiently and 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.

But 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, and 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, saw 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.

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 a condition. Tables are joined using a condition written after ON. Primary and foreign key attributes are usually used here, but variations are possible. Only rows from tables included in the INNER JOIN having a pair, defined by condition, will be returned.

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 takes two tables, the “left” table and the “right” table. The condition is written after ON. It returns all rows from the “left” table, even those that don’t have a pair 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 takes all the rows from the “right” table and joins them to 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 have a pair. Some DBMSs support FULL JOIN, while some 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 but instead a way to use a table more than once in a JOIN. This means that you’ll join the table with itself. So, if you have two tables, one table will be on the left and on the right of the JOIN. Here are some examples of when you would need to do this: “list all employees and all other 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 equations 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 to know what you’re doing).

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 an episode of our We Learn SQL series on Youtube. Check if you already know everything about the different types of JOINs.

Conclusion

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. It will take less or more time based 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.

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.