Back to articles list June 2, 2020 - 7 minutes read How to Learn SQL JOINs Zahin Rahman Nuclear Engineer by day and Data Scientist by night, Zahin is passionate about driving business with data. He has 5+ years of industry experience in energy and power generation and has a master’s degree in Engineering with a focus in Data Science. He loves learning, whether from an industry veteran or a high school science enthusiast. Tags: sql learn sql joins JOINs are one of the most fundamental and commonly used SQL commands for manipulating and querying real-world data sets. They are also one of the more challenging SQL topics to master! In this article, you’ll learn an effective strategy to learn the different types of SQL JOINs. When data is stored in a single table, we can easily retrieve rows and get all the information we need. But when businesses scale, so does their data. And in reality, data is typically split across multiple tables in a relational database. In cases where the data spans multiple tables, we first have to join those tables before we can select and use any data. What Are SQL JOINs? As you progress from an SQL beginner and start working on real-world datasets, you will find yourself needing to combine data from more than one table. To do this, you will need to learn the SQL JOIN command and all its different variants. JOINs are used in SQL queries to link records from two tables based on a common unique key. Usually, we use a combination of primary and foreign keys to link the tables. SQL JOINs can often be a daunting concept to grasp, especially if you are just starting out. The various types of JOINs and their use cases makes them even more challenging to visualize. Types of SQL JOINs In SQL, JOINs are quite a broad topic. What distinguishes each JOIN type from another are the rows returned when the joining condition is met (or not met). To specify which JOIN type to use, add its name – i.e. INNER, LEFT, RIGHT, FULL, or CROSS – just before the JOIN command. The following is a brief overview of the different types of JOINs: An INNER JOIN returns rows when the join condition is satisfied in both tables. In other words, it returns only those records that match the join condition in both tables. (Learn more about INNER JOINs here.) This is the most common type of SQL JOIN. It’s also the default when you don’t specify the type of JOIN. An OUTER JOIN returns all the rows from one table and some or all of the rows from another table. (Learn more about OUTER JOINs here.) There are three types of outer joins: A LEFT JOIN returns all rows from the left table, even if no matching rows have been found in the right table. If there are no matches in the right table, the query will return NULL values for those columns. One of our previous articles provided a more in-depth look at LEFT JOINs that I think you’ll find very useful. A RIGHT JOIN returns all rows from the right table. If there are no matches in the left table, NULL values are returned for those columns. A FULL JOIN is essentially a combination of LEFT JOIN and RIGHT JOIN. It returns all rows from both tables. Where no match is found in either the right or left table, it returns NULLs for those columns. In other words, it is the union of columns of the two tables. A CROSS JOIN, often referred to as a Cartesian JOIN, returns every possible combination of rows from the tables that have been joined. Since it returns all combinations, this is the only JOIN that does not need a join condition and therefore does not have an ON clause. Find out more about CROSS JOINs here. You can also learn about SQL JOINs and their types in our YouTube series We Learn SQL. Watch the clip below. Subscribe to our channel and leave a comment! JOINs can be used to connect more than two tables. Multiple JOINs are queries that contain more than one JOIN clause. They can have the same type of JOIN or mix different JOIN types. These queries give you the ability to combine multiple tables. They’re made by simply adding additional JOIN clauses to your SELECT statement. To join multiple tables through this method, there must be a logical relationship between the tables. For a more specific use case, I recommend referring to our article on how to join 3 tables (or more) in SQL. Mastering SQL JOINs Mastering any technical craft requires solving real-world challenges and is anchored by a sound technical foundation. To learn SQL JOINs, that means: Learning the basics The way some of these JOINs work is often difficult to envision, so it’s definitely worthwhile looking into specific examples for each of them. One of our previous articles on the different types of SQL JOINs provides detailed visual examples. By going through the examples, you learn to appreciate the uniqueness of each one and how they are implemented through the JOIN syntax. Although the above-listed JOINs account for most use cases, real-world applications can at times warrant the implementation of more complex JOIN types. We will not be delving into them in this article, but check out our earlier articles covering the non-equi JOIN and SELF JOIN. You’re probably eager to dive in and put your developing skills to the test as you learn SQL JOINs. However – having gone through my own learning journey and experienced the same eagerness – I would advise you to absorb your new knowledge well first. Give it some practice before you start on real databases. In my opinion, one of the best ways to learn the basics of SQL JOINs is the SQL Basicsc ourse and its sections on JOINs (modules 3 and 5). You must be able to compare and contrast the JOIN types and understand how differently each of them returns its results. Try to visualize the interaction between the tables being joined; this course will help you with that. Practicing on Real Data Sets SQL JOINs can be quite a tricky concept to master, especially for beginners; for one thing, learning and practice materials must reflect real-world use cases for learners to properly grasp the material. Even so, JOINS in real life are often more complicated than textbook examples. Once you’ve learned the basics, you need to practice writing SQL JOINs in realistic scenarios. It may be challenging for individuals to find suitable data sets. I can totally relate to that – I went through the same thing myself. Also, it’s hard to come up with your own exercises that cover the different types of SQL JOINs. LearnSQL.com’s SQL JOINs course provides in-depth content on learning and practicing JOINs in an interactive and structured environment. The datasets are realistic and the exercises resemble real-world problems. You will not be required to download anything; everything is done in your browser. This course gives you all the JOIN knowledge needed to maneuver the dataset. It concludes with a module designed to cement your knowledge. Specifically, this course teaches you to: JOIN data from more than one table. JOIN multiple tables in a single query. JOIN a table with itself. JOIN tables on non-key columns. Understand the use cases of LEFT JOIN, RIGHT JOIN, FULL JOIN, and non-equi JOINs. Filter data with different types of JOINs. Additional Tips for Learning SQL JOINs I found it quite useful to start off with two tables. I joined them using different join types, one at a time, and compared each result with my expected results. For this exercise, it’s best to use a smaller dataset: it’s easier for you to visualize the differences and really understand what happens ‘under the hood’. You might also want to look at an Entity-Relationship Diagram (ERD) or a DB schema diagram that outlines all of the table relationships. This can help you visualize how to join tables together in your SQL queries. When practicing SQL JOINs, try to think ahead in terms of the size of the tables you are working with. From a computational performance perspective, when you are joining two tables, it is important to consider the order of the tables in the JOIN. If you notice that one of the tables is considerably larger than the other, you may want to rewrite your query so that the larger table is placed last. You Can Learn SQL JOINs! If you think SQL JOIN statements are beyond your reach, think again! Although the JOIN statement is often perceived as complicated, it’s a powerful and time-saving technique. JOINs might seem daunting at first, but you will get the hang of them. With the right learning and practice materials, you’ll even find yourself mastering SQL JOINs. I am confident that the learning strategies and resources discussed above will help you. Once you have mastered SQL Basics and JOINs courses, you can elegantly maneuver within even the most complex databases. Tags: sql learn sql joins You may also like 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 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 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 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 How to Practice SQL JOINs Do you want to master SQL JOINs? The best way is through practice. Here’s how to practice SQL JOINs along with some examples. 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 Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.