Back to articles list June 9, 2021 - 7 minutes read 4 Simple Ways to Finally Learn Which JOIN to Use in a SQL Query Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql sql joins You probably already know that you should use JOIN to combine data from several tables. But what kind of JOIN? In this article, I’ll introduce four simple ways to finally learn which JOIN to use in different scenarios. Before we jump into smart strategies for learning SQL JOINs, I suggest taking a brief overview of the major JOIN types. We’ll go through several examples to recall the difference between (INNER) JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, and FULL (OUTER) JOIN. SQL JOIN Types: A Brief Overview For our JOIN examples, we’ll be using data from a birth center, a midwife-led healthcare facility for mothers in labor. In particular, we have two tables: newborns, which has some basic information on the babies born during a certain period. midwives, with the names and experience information on the midwives working in our birth center. Here are the tables: newborns idmother_namegenderweightheightmidwife_id 11Marta StewartMale3.452.0104 12Cathrine BlackFemale3.048.5101 13Julia GreyFemale2.848.1104 14Penelope GreenMale3.852.5105 15Gabriela GarciaMale2.948.8105 16Nikita MooreMale4.053.0102 17Lucy MillerFemale2.547.3101 midwives idfirst_nameyears_experience 101Samantha White6 102Kathleen Webster2 103Sarah McCarty1 105Julia Smith4 106Angela Phillips0 Now, let’s move straight to the examples. INNER JOIN We’ll start with the most common JOIN type, which is INNER JOIN (or simply JOIN). This join type is used to display matching records from both tables. Let’s say we want to get the newborn’s id, the name of their mother, and also the name of the midwife who was helping during labor. To get this information, we can join the newborns and midwives tables based on the midwife’s ID, which is available in both tables: SELECT n.id, n.mother_name, m.name AS midwife FROM newborns n JOIN midwives m ON n.midwife_id = m.id; To reduce typing, we use aliases for table names: n for newborns and m for midwives. We also use a column alias (m.name AS midwife) to get a meaningful column name in the output. Here is the result: idmother_namemidwife 12Cathrine BlackSamantha White 16Nikita MooreKathleen Webster 17Lucy MillerSamantha White 14Penelope GreenJulia Smith 15Gabriela GarciaJulia Smith We got the information we wanted. However, you may notice that some newborns (IDs 11 and 13) are missing from our result table. That means that the corresponding records (i.e. midwife IDs) have not been identified in the second table (i.e. midwives). If you want to keep all the records from the first table, you should use LEFT JOIN. LEFT JOIN LEFT JOIN or LEFT OUTER JOIN is used to display all records from the first (left) table and matching records from the second (right) table. To keep all the newborns in the output, we can use the same query as above, simply replacing JOIN with LEFT JOIN: SELECT n.id, n.mother_name, m.name AS midwife FROM newborns n LEFT JOIN midwives m ON n.midwife_id = m.id; And the result: idmother_namemidwife 12Cathrine BlackSamantha White 14Penelope GreenJulia Smith 11Marta StewartNULL 13Julia GreyNULL 17Lucy MillerSamantha White 16Nikita MooreKathleen Webster 15Gabriela GarciaJulia Smith Now we have all the records from the newborns table displayed in the output. At the same time, there are two records with NULL values in the midwife column. Apparently, the corresponding midwife (ID 104) is not listed in the midwives tables. Maybe she has recently started working at another birth center. Great! But it’s also interesting to see if there are any midwives in our birth center that have not assisted with recent labors. Let’s find out using RIGHT JOIN. RIGHT JOIN RIGHT JOIN or RIGHT OUTER JOIN is used to display all records from the second (right) table and matching records from the first (left) table. If we want to keep all records from the midwives table, we can use the exact same SQL query as before, merely replacing LEFT JOIN with RIGHT JOIN: SELECT n.id, n.mother_name, m.name AS midwife FROM newborns n RIGHT JOIN midwives m ON n.midwife_id = m.id; Note that RIGHT JOIN is rarely used because it can be usually replaced with LEFT JOIN. In our case, we could use LEFT JOIN, and switch the order of tables, i.e. putting midwives in the FROM clause and newborns in the LEFT JOIN. The results would be the same as below: idmother_namemidwife 12Cathrine BlackSamantha White 14Penelope GreenJulia Smith 15Gabriela GarciaJulia Smith 16Nikita MooreKathleen Webster 17Lucy MillerSamantha White NULLNULLSarah McCarty NULLNULLAngela Phillips In the resulting table, you can see that there are two midwives without matching records in the newborns table: Sarah McCarty and Angela Phillips. It looks like these two midwives have the least experience and are probably assisting the more experienced midwives without being assigned a major role. Notice also that we again don’t see all the newborns in the result set. What if we want to keep all records from both tables? This is when FULL JOIN should be used. FULL JOIN FULL JOIN or FULL OUTER JOIN is used to keep all the records from both tables. So, if we want to keep all newborns and all midwives in the result set, we can just replace the RIGHT JOIN from our last example with a FULL JOIN. Note also that the order of tables doesn’t matter with FULL JOIN; we can switch the position of the tables and get the same result: SELECT n.id, n.mother_name, m.name AS midwife FROM newborns n FULL JOIN midwives m ON n.midwife_id = m.id; idmother_namemidwife 11Marta StewartNULL 12Cathrine BlackSamantha White 13Julia GreyNULL 14Penelope GreenJulia Smith 15Gabriela GarciaJulia Smith 16Nikita MooreKathleen Webster 17Lucy MillerSamantha White NULLNULLSarah McCarty NULLNULLAngela Phillips Now we have some NULL values in all columns; these indicate that some of the left-table records were not matched in the second table or that some of the right-table records were not matched in the first table. All these JOIN principles don’t sound too challenging, do they? Still, selecting the correct JOIN to use is often a challenge for beginners. So, let’s discover the best strategies for mastering SQL JOINs. 4 Ways to Learn SQL JOINs Here are four steps to finally mastering the different types of SQL JOINs. Learn the key differences between the JOIN types. Your first step should be to learn about the four major JOIN types and the very basic rules of applying them: JOIN displays only the matching records from both tables. LEFT JOIN displays all the records from the left table and matching records from the right table. RIGHT JOIN displays all the records from the right table and matching records from the left table. FULL JOIN displays all the records from both tables. Read high-quality articles on SQL JOINs. After you’ve learned the basics, we recommend going through the details and use cases for different types of JOINs. I suggest you start with these articles that discuss all major JOIN types and include detailed explanations and examples: SQL JOIN Types Explained What Is an SQL INNER JOIN? What Is LEFT JOIN in SQL? What FULL JOIN Is and When to Use It? 7 SQL JOIN Examples with Detailed Explanations Use our SQL JOIN Cheat Sheet. Even if you know SQL JOINs fairly well, it’s often helpful to see a brief reminder with the syntax and use cases for different JOIN types. I recommend printing or bookmarking our amazing SQL JOIN Cheat Sheet. It is an illustrated guide to SQL JOINs that you can look up whenever you’re not sure how to use JOIN in your SQL query. Practice! Finally, if you really want to master SQL JOINs, you need lots of practice. You can read about the challenges you may encounter when practicing SQL JOINs. To make your first experience with SQL JOINs as smooth as possible, I suggest starting with LearnSQL.com’s interactive SQL JOINs course. This hands-on course includes 93 coding challenges that cover all important types of SQL JOINs. Specifically, you’ll learn: When to use JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. How to join three or more tables in one query. How to filter data with different join types. How to join a table with itself. What a non-equi join is and when to use it. You can learn more about this course in this overview article. Thanks for reading, and happy learning! Tags: sql learn sql sql joins You may also like SQL JOIN Types Explained Learn how to join tables in SQL. Review the different SQL join types and when to use inner join, left join, right join, or full join. Read more What Is an SQL INNER JOIN? Learn how to combine data from two tables with the SQL JOIN operator. Read more What Is LEFT JOIN in SQL? How does LEFT JOIN work in SQL? Master LEFT OUTER JOIN with animated illustrations and multiple examples. Read more What FULL JOIN Is and When to Use It Learn what a SQL FULL OUTER JOIN is, how to implement it, and its unique use cases as compared to other types of SQL JOINs. Read more 7 SQL JOIN Examples With Detailed Explanations Find detailed explanations of how the different SQL JOIN types work, clear descriptions of the syntax, and a thorough discussion of the results. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.