Back to articles list March 24, 2020 - 9 minutes read How to Practice SQL JOINs Emil Drkušić INSERT INTO author (first_name, last_name) VALUES (‘Emil’, ‘Drkušić’); INSERT INTO experience (details) VALUES (‘15+ years experience with databases’), (‘database design’), (‘data analytics’), (‘hands on experience on many projects (in IT, finance, telco)’), (‘lecturing’), (‘writing articles’), (‘technologies: SQL, MySQL, SQL Server, MS Access, MS Excel, VBA, C/C++/C#, Python’) ; INSERT INTO personal_info (details) VALUES (‘from: Zagreb, Croatia, Europe’) (‘hobbies: friends & family, hiking, chess, philately, history’); Tags: sql learn sql join online practice 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: You will need to know how to create it—take care of the data, keys, inserts, etc. 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. 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. Tags: sql learn sql join online practice You may also like How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more How to LEFT JOIN Multiple Tables in SQL You will learn how to left join 3 tables in SQL while avoiding common mistakes in joining multiple tables. Examples included! Read more SQL INNER JOIN Explained in Simple Words Learn how to join tables in SQL using the most famous among SQL JOIN types–the INNER JOIN. Read more Converting Subqueries to Joins No more badly performed queries! Find out how you can convert SQL subqueries to joins and improve your query efficiency. Read more An Illustrated Guide to Multiple Join Did you wonder what is the most efficient way of making multiple joins in SQL? We did as well - come by and check out our illustrated guide! Read more An Illustrated Guide to the SQL Non Equi Join SQL join doesn’t have to be based on identical matches! In this post, we look at the non-equi join, which uses ‘non-equal’ operators to match records. Read more An Illustrated Guide to the SQL Self Join What is a SQL self join and how does it work? When should you use a self join in SQL? In this article, you’ll find answers to these questions! Read more An Illustrated Guide to the SQL OUTER JOIN An SQL JOIN clause links the data from two or more database tables. Let’s find out what is the OUTER JOIN and how does it work! Read more An Illustrated Guide to the SQL INNER JOIN INNER JOIN combines data from multiple tables by joining them based on a matching record. It requires a JOIN condition, which we'll explain in this article. Read more An Illustrated Guide to the SQL CROSS JOIN CROSS JOIN returns a Cartesian product, or all records joined to all records in all tables. Take a look at the example queries. Read more An Introduction to Using SQL Aggregate Functions with JOINs Aggregate functions. Powerful SQL tools. Let's see how they cooperate paired with LEFT JOIN, SUM and GROUP BY perform computations on multiple tables. Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more SQL JOINs A SQL JOIN is a method to retrieve data from two or more database tables. Here we present a basic overview of what data from a particular SQL join will look Read more SQL JOINs for Beginners JOIN data from two or more tables with no effort. Read more How to LEFT JOIN Multiple Tables in SQL You will learn how to left join 3 tables in SQL while avoiding common mistakes in joining multiple tables. Examples included! Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more An Illustrated Guide to the SQL Self Join What is a SQL self join and how does it work? When should you use a self join in SQL? In this article, you’ll find answers to these questions! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.