Back to articles list August 17, 2017 - 4 minutes read An Illustrated Guide to the SQL OUTER JOIN Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: join SQL basics SQL clauses We’ve already discussed the SQL CROSS JOIN and INNER JOIN statements. It’s time to explore another: OUTER JOIN. What is it? How does it work? Let’s find out! If you’ve read our other posts, you know that you can link the data in two or more database tables using one of the many types of SQL join operator. Today, we’ll discuss the three kinds of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. These join records based on matching row values, but they do it a bit differently than other join statements. What Is an SQL OUTER JOIN? To answer this question, we have to delve into the different types of OUTER JOIN: LEFT OUTER JOIN returns every record in the left table and all matching records from the right table. If there’s no match found, a NULL is shown next to the unmatched record. RIGHT OUTER JOIN returns every record in the right table and all matching records from the left table. If there’s no match found, a NULL is shown next to the unmatched record. FULL OUTER JOIN returns all records from both tables. All unmatched records are paired with NULLs. Now let’s look at the tables we will use to illustrate these operators. The “shirt” table only has one field, “color_shirt”: color_shirt yellow green blue The “pants” table also has one field, “color_pants”: color_pants pink green blue These tables are self-explanatory. Just imagine them as two parts of an outfit: the color of your pants and the color of your shirt. The idea is to find shirts and pants with identical colors. Good to Know: The left table is the first table listed and is found after the FROM clause. The right table is the second table listed and is found after the JOIN clause. You can usually omit the OUTER keyword in any OUTER JOIN — FULL JOIN, LEFT JOIN, and RIGHT JOIN will work just as well in many databases (but check your documentation to be sure). Using a LEFT OUTER JOIN The LEFT OUTER JOIN retrieves all records from the first (left) table and matches them to records from the second (right) table. Any non-matching records from the left table are also selected, but with NULL values where the right table records would be. Have a look at the example. SELECT color_shirt, color_pants FROM shirt LEFT JOIN pants ON color_shirt=color_pants; The left table (after FROM) is “shirt” and the right table (after LEFT JOIN) is “pants”. The ON predicate states the condition for matching records from “shirt” with records from “pants”. This condition is that the values in the “shirt”.“color_shirt” field and those in the “pants”.“color_pants” field must match. If there is no match, records from the “shirt” table will be shown, but a NULL value is set where the matching “pants” record would be. These are the results from this query: color_shirt color_pants yellow NULL green green blue blue And here is an illustration of how this query works and what the results look like: As you see, the matching green and blue outfits are together. The yellow shirt has no pants because the “pants” table does not have any fields with a “yellow” value. Using a RIGHT OUTER JOIN The RIGHT OUTER JOIN works like the LEFT JOIN, but with one major difference: it selects all records from the right table (in this case, “pants”). The records from the left table (“shirt”) will only be shown if they match. Look at the query: SELECT color_shirt, color_pants FROM shirt RIGHT JOIN pants ON color_shirt=color_pants; Here is the result: color_shirt color_pants NULL pink green green blue blue And here is the illustration of using RIGHT JOIN and its results. All the pants are shown, but there is no matching shirt for the pink pair of pants. Using a FULL OUTER JOIN Let’s recap what we’ve done so far. With LEFT JOIN, all shirts and any matching pants were returned. With RIGHT JOIN, all pants and any matching shirts were returned. What will happen if you use a FULL OUTER JOIN? It shows all records from both tables. If possible, it will match the records; if not, a NULL will be shown where the matching record would be. Let’s look an example query: SELECT color_shirt, color_pants FROM shirt FULL JOIN pants ON color_shirt=color_pants; Notice that in a FULL JOIN which table is left and which is right is negligible. The result will be the same. Here is the result: color_shirt color_pants yellow NULL green green blue blue NULL pink The result set contains all the records stored in the “shirt” table and in the “pants” table. The picture shows that FULL JOIN returned all possible clothes: all shirts and all pants. The matching pairs (green and blue) are shown together, and the unmatched items (yellow shirt and pink pants) are shown separately. Want to Learn More About OUTER JOINs? There’s more to discover about using OUTER JOINS. To learn more, check out LearnSQL.com’s SQL Basics course. Tags: join SQL basics SQL clauses You may also like 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 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 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 What Is Vertabelo’s SQL Cheat Sheet? Rock the SQL! You don’t have to be a programmer to master SQL. Download the SQL Cheat Sheet and find quick answers for the common problems with SQL queries. Read more Common SQL Window Functions: Positional Functions Positional SQL window functions deal with data's location in the set. In this post, we explain LEAD, LAG, and other positional functions. Read more SQL Window Function Example With Explanations Interested in how SQL window functions work? Scroll down to see our SQL window function example with definitive explanations! 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 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 Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.