Back to articles list November 12, 2020 - 10 minutes read SQL JOIN Types Explained Marija Ilic Marija works as a data scientist in the banking industry. She specializes in big data platforms (Cloudera and Hadoop) with software and technologies such as Hive/Impala, Python and PySpark, Kafka, and R. Marija has an extensive background in DWH/ETL development in the banking industry. Her main interests are predictive modeling, real-time decision-making, and social network analysis. Outside of work, Marija enjoys listening to her favorite LPs on her old gramophone—and never grows tired of its soothing crackle. Tags: sql learn sql join What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN in SQL? When should you use each one? We’ve got your answers right here. You want to combine data from two or more different tables, but you’re not sure how to do it in SQL. No worries. In this article, I will show you how to use the SQL JOIN clause to merge data from two tables. There are different SQL JOIN types that you can use for different results. If you want to learn the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, keep reading. This article is for you. First, let’s start with the basics: what is a JOIN statement and how does it work? What Is an SQL JOIN? A JOIN clause is used when you need to combine data from two or more tables into one data set. Records from both tables are matched based on a condition (also called a JOIN predicate) you specify in the JOIN clause. If the condition is met, the records are included in the output. In this article, I’ll explain the SQL JOIN concept and the different JOIN types using examples. So, before we go any further, let's take a look at the tables that we are going to use in this article. Get to Know the Database We are going to use tables from a fictional bank database. The first table is called account and it contains data related to customer bank accounts: account_idoverdraft_amtcustomer_idtype_idsegment 25568891200042RET 1323598795155011RET 2225546500052RET 5516229600045RET 5356222750055RET 2221889540012RET 245568812500502CORP 13224886562500511CORP 13235987953100521CORP 13231115951220531CORP account table This table contains 10 records (10 accounts) and five columns: account_id – Uniquely identifies each account. overdraft_amount – The overdraft limit for each account. customer_id – Uniquely identifies each customer. type_id – Identifies the type of that account. segment – Contains the values ‘RET’ (for retail clients) and ‘CORP’ (for corporate clients). The second table is called customer and contains customer-related data: customer_idnamelastnamegendermarital_status 1MARCTESCOMY 2ANNAMARTINFN 3EMMAJOHNSONFY 4DARIOPENTALMN 5ELENASIMSONFN 6TIMROBITHMN 7MILAMORRISFN 8JENNYDWARTHFY customer table This table contains eight records and five columns: customer_id – Uniquely identifies each account. name – The customer’s first name. lastname – The customer’s last name. gender– The customer’s gender (M or F). marital_status – If the customer is married (Y or N). Now that we have these two tables, we can combine them to display additional results related to customer or account data. JOIN can help us to get answers to questions like: Who owns each account in the account table? How many accounts does Marc Tesco have ? How many accounts are owned by a female customer? What is the total overdraft amount for all of Emma Johnson’s accounts? To answer each of these questions, we need to combine two tables (account and customer) using a column that appears in both tables (in this case, customer_id). Once we merge the two tables, we will have account and customer information in a single output. Keep in mind that in the account table we have some customers that can’t be found in the customer table. (Info about corporate clients is stored somewhere else.) Also, keep in mind that some customer IDs are not present in the account table; some customers don't have accounts. There are several ways we can combine two tables. Or, put another way, we can say that there are several different SQL JOIN types. SQL’s 4 JOIN Types SQL JOIN types include: INNER JOIN (also known as a ‘simple’ JOIN). This is the most common type of JOIN. LEFT JOIN (or LEFT OUTER JOIN) RIGHT JOIN (or RIGHT OUTER JOIN) FULL JOIN (or FULL OUTER JOIN) Self joins and cross joins are also possible in SQL, but we won’t talk about them in this article. For more info, see An Illustrated Guide to the SQL Self Join and An Illustrated Guide to the SQL Cross Join. Let's dive deeper into the first four SQL JOIN types. I will use an example to explain the logic and the syntax of each type. Sometimes people use Venn diagrams when explaining SQL JOIN types. I'm not going to use them here, but if that’s your thing then check out the article How to Learn SQL JOINs. INNER JOIN INNER JOIN is used to display matching records from both tables. This is also called a simple JOIN; if you omit the INNER keyword (or any other keyword, like LEFT, RIGHT, or FULL) and just use JOIN, this is the type of join you’ll get by default. There are usually two (or more) tables in a join statement. We call them the left and right tables. The left table is in the FROM clause – and thus to the left of the JOIN keyword. The right table is between the JOIN and ON keywords, or to the right of the JOIN keyword. If the JOIN condition is met in an INNER JOIN, that record is included in the data set. It can be from either table. If the record does not match the criteria, it’s not included. The image below shows what would happen if the color blue was the join criteria for the left and right tables: Let's take a look how INNER JOIN works in our example. I’m going to do a simple JOIN on account and customer to display account and customer information in one output: SELECT account.*, customer.name, customer.lastname, customer.gender, customer.marital_status FROM account JOIN customer ON account.customer_id=customer.customer_id; Here is a short explanation of what’s going on: I’m using JOIN because we are merging the account and customer tables. The JOIN predicate here is defined by equality: account.customer_id = customer.customer_id In other words, records are matched by values in the customer_id column: Records that share the same customer ID value are matched. (They are shown in color in the above image.) Records that don’t have a match in either table (shown in gray) are not included in the result set. For records that have a match, all attributes from the account table are displayed in the result set. The name, last name, gender, and marital status attributes from the customer table are also displayed. After running this code, SQL returns following: account_idoverdraft_amountcustomer_idtype_idsegmentnamelastnamegendermarital_status 25568891200042RETDARIOPENTALMN 1323598795155011RETMARCTESCOMY 2225546500052RETELENASIMSONFN 5516229600045RETDARIOPENTALMN 5356222750055RETELENASIMSONFN 2221889540012RETMARCTESCOMY INNER JOIN result As we mentioned earlier, only colored (matching) records were returned; all others are discarded. In business terms, we displayed all the retail accounts with detailed information about their owners. Non-retail accounts were not displayed because their customer information is not stored in the customer table. LEFT JOIN Sometimes you’ll need to keep all records from the left table – even if some don't have a match in the right table. In the last example, the gray rows were not displayed in the output. Those are corporate accounts. In some cases, you may want to have them in the data set, even if their customer data is left empty. If we would like to return unpaired records from the left table, then we should write a LEFT JOIN. Below, you can see that the LEFT JOIN returns everything in the left table and matching rows in the right table. Here is how the previous query would look if we used LEFT JOIN instead of INNER JOIN: SELECT account.*, customer.name, customer.lastname, customer.gender, customer.marital_status FROM account LEFT JOIN customer ON account.customer_id=customer.customer_id; The syntax is identical. The result, however, is not the same?. Now we can see the corporate accounts (gray records) in the results: account_idoverdraft_amountcustomer_idtype_idsegmentnamelastnamegendermarital_status 25568891200042RETDARIOPENTALMN 1323598795155011RETMARCTESCOMY 2225546500052RETELENASIMSONFN 5516229600045RETDARIOPENTALMN 5356222750055RETELENASIMSONFN 2221889540012RETMARCTESCOMY 245568812500502CORPNULLNULLNULLNULL 13224886562500511CORPNULLNULLNULLNULL 13235987953100521CORPNULLNULLNULLNULL 13231115951220531CORPNULLNULLNULLNULL Left join - account with customer Notice how attributes like name, last name, gender, and marital status in the last four rows are populated with NULLs. This is because these gray rows don’t have matches in the customer table (i.e. customer_id values of 50, 51 ,52 , and 53 are not present in the customer table). Thus, those attributes have been left NULL in this result. RIGHT JOIN Similar to LEFT JOIN, RIGHT JOIN keeps all records from the right table (even if there is no matching record in the left table). Here’s that familiar image to show you how it works: Once again, we use the same example. However, we’ve replaced LEFT JOIN with RIGHT JOIN: SELECT account.account_id, account.overdraft_amount, account.type_id, account.segment, account.customer_id, customer.customer_id customer.name, customer.lastname, customer.gender, customer.marital_status FROM account RIGHT JOIN customer ON account.customer_id=customer.customer_id; The syntax is mostly the same. I’ve made one more small change: In addition to account.customer_id, I’ve also added customer.customer_id column to the result set. I did this to show you what happens to records from the customer table that don't have a match on the left (account) table. Here is the result: account_idoverdraft_amounttype_idsegmentcustomer_idcustomer_idnamelastnamegendermarital_status 132359879515501RET11MARCTESCOMY 222188954002RET11MARCTESCOMY NULLNULLNULLNULLNULL2ANNAMARTINFN NULLNULLNULLNULLNULL3EMMAJOHNSONFY 2556889120002RET44DARIOPENTALMN 551622960005RET44DARIOPENTALMN 222554650002RET55ELENASIMSONFN 535622275005RET55ELENASIMSONFN NULLNULLNULLNULLNULL6TIMROBITHMN NULLNULLNULLNULLNULL7MILAMORRISFN NULLNULLNULLNULLNULL8JENNYDWARTHFY RIGHT JOIN result As you can see, all records from the right table have been included in the result set. Keep in mind: Unmatched customer IDs from the right table (numbers 2,3, 6,7, and 8, shown in gray) have their account attributes set to NULL in this result set. They are retail customers that don’t have a bank account – and thus no records in the account table. You might expect that the resulting table will have eight records because that is the total number of records in the customer table. However, this is not the case. We have 11 records because customer IDs 1, 4, and 5 each have two accounts in the account table. All possible matches are displayed. FULL (OUTER) JOIN I’ve shown you how to keep all records from the left or right tables. But what if you want to keep all records from both tables? In our case, you’d want to display all matching records plus all corporate accounts plus all customers without accounts. To do this, you can use FULL OUTER JOIN. This JOIN type will pair all matching columns and will also display all unmatching columns from both tables. Unfamiliar attributes will be populated with NULLs. Have a look at the image below: Here is the FULL OUTER JOIN syntax: SELECT account.*, CASE WHEN customer.customer_id IS NULL THEN account.customer_id ELSE customer.customer_id END customer_id customer.name, customer.lastname, customer.gender, customer.marital_status FROM account FULL JOIN customer ON account.customer_id=customer.customer_id; Now the result looks like this: account_idoverdraft_amountcustomer_idtype_idsegmentnamelastnamegendermarital_status 25568891200042RETDARIOPENTALMN 1323598795155011RETMARCTESCOMY 2225546500052RETELENASIMSONFN 5516229600045RETDARIOPENTALMN 5356222750055RETELENASIMSONFN 2221889540012RETMARCTESCOMY 245568812500502CORPNULLNULLNULLNULL 13224886562500511CORPNULLNULLNULLNULL 13235987953100521CORPNULLNULLNULLNULL 13231115951220531CORPNULLNULLNULLNULL NULLNULL2NULLNULLANNAMARTINFN NULLNULL3NULLNULLEMMAJOHNSONFY NULLNULL6NULLNULLTIMROBITHMN NULLNULL7NULLNULLMILAMORRISFN NULLNULL8NULLNULLJENNYDWARTHFY Full outer join result Notice how the last five rows have account attributes populated with NULLs. This is because these customers do not have records in the account table. Notice also how customers 50, 51, 52, and 53 have first or last names and other attributes from the customer table populated with NULLs. This is because they don't exist in the customer table. Here, customer_id in the result table is never NULL because we defined customer_id with a CASE WHEN statement: CASE WHEN customer.customer_id IS NULL THEN account.customer_id ELSE customer.customer_id END customer_id This actually means that customer_id in the result table is a combination of account.customer_id and customer.customer_id (i.e. when one is NULL, use the other one). We could also display both columns in the output, but this CASE WHEN statement is more convenient. It’s okay if you are confused by all the different SQL JOINs and what they do. Just stick with it. I’d recommend that you look at our SQL JOIN Cheat Sheet. Keep it near you; it is very useful when coding. It’s also helpful to review our article on practicing SQL JOINs. The more you learn and practice, the clearer SQL JOINs will be. Up Next: Practice SQL JOINs In this article, we’ve introduced different SQL JOIN types. Inner, left, right, and full joins all return different results. Now you need to put that knowledge into action! At LearnSQL.com, you can find more examples to practice on. Our interactive SQL Queries and SQL JOINs courses cover JOIN topics, so I encourage you to try them out. Tags: sql learn sql join 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 How to Join the Same Table Twice Learn when and why you need to join the same table twice in SQL, including when you should use self joins. Read more SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. 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 What's the Difference Between Having Multiple Tables in FROM and Using JOIN? What’s your approach to SQL JOINs? Do you use JOIN or simply list tables in FROM? Learn why SQL practitioners prefer the new syntax with the JOIN keyword. Read more Can you Join two Tables Without a Common Column? Do you need to combine two tables without a common column? Learn how the CROSS JOIN and the UNION operators can help you with this task. Read more Practical Examples of When to Use Non-Equi JOINs in SQL Do you only use equals in your JOIN conditions? Learn how non-equi JOINs can assist with listing duplicates and joining tables based on a range of values. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.